PostgreSQL – Aggregate Objects into JSON Array

jsonpostgresql

I'm sorry for the vague title, but I simply don't know the right words to describe this.

I have this query that transforms a bunch of columns into an object that works just fine:

SELECT row_to_json(t)
FROM (
SELECT type, properties, geometry FROM "bgbCargoMinardJSON"
) t

However, I want to group the objects that fall within a certain category into an array. This category is defined by a fourth column inside my table named "cargoProductId". The array should have the value of "cargoProductId" as the key. So:

"961":[
{"type":"Feature",....
{"type":"Feature",....
{"type":"Feature",....
],
"962":[
.....
]

So I've been struggling with this for the last 1 1/2 hours or so. I really have no clue how to do this. This is what I have right now:

SELECT array_agg(row_to_json(t))
FROM (
SELECT type, properties, geometry FROM "bgbCargoMinardJSON"
) t) FROM "bgbCargoMinardJSON" GROUP BY "carProductId"

Best Answer

If you are on 9.4 something like this might be what you are after:

select json_object(array_agg(id)::text[],array_agg(rw)::text[])
from( select id
           , ( select to_json(array_agg(row_to_json(t)))
               from (select typ,prop from bgb where id=b.id) t ) rw
      from bgb b
      group by id ) z;
Related Question