patternsqlModerate
Aggregate objects into a json array (subquery issues)
Viewed 0 times
objectsarrayintoissuessubqueryjsonaggregate
Problem
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:
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:
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:
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"
) tHowever, 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"Solution
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;Code Snippets
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;Context
StackExchange Database Administrators Q#102085, answer score: 12
Revisions (0)
No revisions yet.