0

I'm building a JSON object from various tables. I have successfully got the following code to almost do what I want. The only problem is the the aggregated array is set as another object, and I just want the item data in the main object to point to the array of rows selected.

SELECT jsonb_build_object('ok',false,'data',f1) FROM 
    (SELECT array_agg(f2) FROM (
    SELECT c.catalogid,cl.title,cl.shortname FROM
    shopmaster.catalog c LEFT JOIN
    shopmaster.catalog_lang cl ON c.catalogid=cl.catalogid LEFT JOIN
    shopmaster.lang l ON cl.langid=l.langid
    WHERE inherit_from=3 AND code='en') f2) f1;

This returns the following json

{"ok": false, "data": {"array_agg": [{"title": "Vitamins", "catalogid": 6, "shortname": "vit"}, {"title": "Cheese Vitamins", "catalogid": 7, "shortname": "chevit"}, {"title": "fruitveg", "catalogid": 8, "shortname": "fruit and veg"}, {"title": "Magazines", (...)

When I want

{"ok": false, "data": [{"title": "Vitamins", "catalogid": 6, "shortname": "vit"}, {"title": "Cheese Vitamins", "catalogid": 7, "shortname": "chevit"}, {"title": "fruitveg", "catalogid": 8, "shortname": "fruit and veg"}, {"title": "Magazines", (...)
whitelined
  • 310
  • 4
  • 13

1 Answers1

0

AS indicated in previous answer, I could have selected the named result, like f1.array_agg. In the end, I used the jsonb_agg() function like so

SELECT jsonb_build_object('ok',false,'data',jsonb_agg(f1)) FROM (
    SELECT c.catalogid,cl.title,cl.shortname FROM
    shopmaster.catalog c LEFT JOIN
    shopmaster.catalog_lang cl ON c.catalogid=cl.catalogid LEFT JOIN
    shopmaster.lang l ON cl.langid=l.langid
    WHERE inherit_from=3 AND code='en') f1
whitelined
  • 310
  • 4
  • 13