I have next resultset from Postgres data base : actually I never work with postgres json and I naven't found any same examples
id | another_id
--------------
1 | 10
1 | 11
3 | 20
3 | 21
3 | 22
and I need to transform it as a json string:
{"id":1 , "another_id" : {[10,11]}}
{"id":3 , "another_id" : {[20,21,22]}
example with to_json
x=# select to_json(s)
from (
select id,array_agg(another_id) another_id
from s1
group by id
) s;
to_json
----------------------------------
{"id":1,"another_id":[10,11]}
{"id":3,"another_id":[20,21,22]}
(2 rows)
Mind In my example instead of {[10,11]} I get [10,11] which would be syntactically correct for json
You need to combine json aggregation with building json objects:
select json_build_object('id', id, 'another_id', json_agg(another_id))
from the_table
group by id;