How to Convert Query Result to JSON Object Inside Postgres

Question

I have a simple query, SELECT name, grp FROM things; that results in the following table:

 name | grp 
------+-----
 a    | y
 b    | x
 c    | x
 d    | z
 e    | z
 f    | z

I would like to end up with the following single JSON object:

 {y: [a], x: [b,c], z: [d,e,f]}

I feel like I'm closer with the query SELECT grp, array_agg(name) as names FROM things GROUP BY grp; which gives three rows with the "name" condensed into an array, but I don't know where to go from here to get the rows condensed into a single JSON object.

SELECT json_build_object(grp, array_agg(name)) as objects FROM things GROUP BY grp; is maybe slightly closer since that results in a single column result of individual JSON objects like {y: [a]}, but they are still individual objects, so that might not be the right path to go down.

This is using Postgresql 9.4.

azdle · Accepted Answer · 2015-06-04 18:26:22Z

23

It seems the key here is the json_object_agg function which is not listed with the rest of the json functions.

See: http://www.postgresql.org/docs/9.4/static/functions-aggregate.html

The following query gets me exactly what I'm looking for:

SELECT json_object_agg(each.grp, each.names) FROM (
    SELECT grp, array_agg(name) as names FROM things GROUP BY grp
) AS each;

answered Jun 4, 2015 at 18:26

azdle

2,3794 gold badges18 silver badges25 bronze badges

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

2 Comments

azdle Over a year ago

Why is it always the case that I spend a day and a half trying to figure out the answer myself, give up, post to SO, give it bit more searching and find the answer in 10 minutes.

klin Over a year ago

The vastness of the knowledge accumulated here fertilizes your mind.

Collectives™ on Stack Overflow

How to Convert Query Result to JSON Object Inside Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related