CODEWITHSUNDEEP
sqljsonpostgresql
dzrkot
dzrkot

Reputation: 603

postgres transform 2 columns as json with array

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]}

Upvotes: 2

Views: 53

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51659

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

Upvotes: 2

user330315
user330315

Reputation:

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;

http://rextester.com/BCK50498

Upvotes: 2

Related Questions