CODEWITHSUNDEEP
sqlarraysjsonpostgresql
ocavo
ocavo

Reputation: 92

Postgresql jsonb_agg with multiple json

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"},
   ...
  ]
 
}

Upvotes: 0

Views: 2478

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3303

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

Upvotes: 1

Related Questions