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.

Answer 1

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;