HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Aggregate objects into a json array (subquery issues)

Submitted by: @import:stackexchange-dba··
0
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:

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"

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.