Postgresql jsonb_agg with multiple json

Question

Hi i'm using jsonb_agg for create array of objects. But when i want to create it with multiple json im getting this error.

ERROR:  function jsonb_agg(json, json) does not exist

This is what im trying to do

SELECT 
    station.id AS "objectID",
    station.name AS "objectName",
    station.activity,
    'Station'::text AS unit,
    jsonb_agg(
     json_build_object('key'::text, 'value'::text),
     json_build_object('key'::text, 'value'::text),
     ...
    ) AS "childrenList"
    FROM eqp_stations station

Is there any way to merge multiple json into array of objects

I hard coded json key and value pairs but I am going to fill these areas later.

Expected Json object like

{
 "objectId":123,
 "objectName":"blabla",
 "unit":"Station",
 "childrenList": [
   {"key":"value"},
   {"key":"value"},
   ...
  ]
 
}

it's pretty obvious that you have a jsonb function with arguments returning json values. You might try json(b)_build_object functions as arguments... — Barbaros Özhan
– Barbaros Özhan, Commented Mar 15, 2022 at 18:23
Post how the expected json looks like and explain why are 'key' and 'value' hardcoded? — Salman Arshad
– Salman Arshad, Commented Mar 15, 2022 at 18:30
I will fill the key value pairs with join statements. ı added expected output @SalmanA — ocavo
– ocavo, Commented Mar 15, 2022 at 19:21
I suppose json_agg could be replaced with jsonb_build_array and that is is, assuming you know the number of children in advance. — Salman Arshad
– Salman Arshad, Commented Mar 15, 2022 at 19:42
How are we supposed to answer the question now if you don't finish asking it until "later"? — jjanes
– jjanes, Commented Mar 16, 2022 at 14:38

Ramin Faracov · Accepted Answer · 2022-03-15 21:59:46Z

1

select 
    jsonb_agg(t1.*)  
from (
    SELECT 
        station.id AS "objectID",
        station.name AS "objectName",
        station.activity,
        'Station'::text AS unit,
        jsonb_build_array( 
            json_build_object('key'::text, 'value'::text),
            json_build_object('key'::text, 'value'::text)
        ) AS "childrenList"
    FROM 
        eqp_stations station
) t1

answered Mar 15, 2022 at 21:59

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql jsonb_agg with multiple json

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related