8

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"

6

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;
  • Thanks Jack! Formation wise it did the trick! The only issue I'm having though is that it doesn't provide valid json (well technically it does). There's this text to json conversion going on such as: {"3565": "[{\"type\": I have an apostrophe before every bracket and a slash before every apostrophe. I had this before and it's always something with the datatype. However truth to be told, I don't fully understeand how the query you wrote works, and therefore I don't really know where and how to fix this. – ervazu May 21 '15 at 10:25
  • This is probably not the best solution, but I just used the replace function to fix this. Once again thanks for your time! – ervazu May 21 '15 at 11:52
  • 1
    this worked for my case: json_agg(to_json(items.*)) as "items" – ricka Oct 17 '17 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.