CODEWITHSUNDEEP
postgresql
Kelly Flet
Kelly Flet

Reputation: 564

Is there a way to merge these json aggregations?

I am trying to create json object from getting some info from one table, then creating interger arrays from some other tables' id's and adding n > 1 (2 or more) arrays to the json object. I am using Postgres version 10.7:

select json_build_object(
   'id', bi.id,
   'street', ba.street,
   'features1', features1.f1_json_arr,
   'features2', features2.f2_json_arr
)
from business.info bi
         inner join business.address ba on bi.id = ba.location_id
         left outer join (
    select f1.location_id,
           json_agg(f1_id) as f1_json_arr
    from business.features1 as f1
    group by f1.location_id
) features1 on bi.id = features1.location_id
         left outer join (
    select f2.location_id,
           json_agg(f2_id) as f2_json_arr
    from business.feature2 as f2
    group by f2.location_id
) features2 on bi.id = features2.location_id
where bi.id='1234';

which gives me a result as I want, like so:

{
  "id": "1234",
  "street", "some street",
  "features1": [
    2,
    1
  ],
  "features2": [
    3,
    2,
    1
  ]
}

Is there a cleaner way to do this? I tried this:

select json_build_object(
               'id', bi.id,
               'street', ba.street_name,
               'features1', f1_and_f2.f1_json_arr,
               'features2', f1_and_f2.f2_json_arr
           )
from business.info bi
         inner join business.address ba
                    on bi.id = ba.location_id
         left outer join (
    select f1.location_id,
           json_agg(f1_id)  as f1_json_arr,
           json_agg(f2_id) as f2_json_arr
    from business.feature1 as f1
             inner join business.feature2 as f2 on f1.location_id = f2.location_id
    group by f1.location_id
) f1_and_f2 on bi.id = f1_and_f2.location_id
where bi.id = '1234';

but got a result like this:

{
  "id": "1234",
  "street_name": "a street",
  "features1": [
    2,
    2,
    2,
    1,
    1,
    1
  ],
  "features2": [
    3,
    2,
    1,
    3,
    2,
    1
  ]
}

Upvotes: 2

Views: 234

Answers (1)

Thiago Mata
Thiago Mata

Reputation: 2959

SELECT A.*, B.*, C_GROUPED.C_STUFF, D_GROUPED.D_STUFF
FROM A
INNER JOIN B ON B.A_ID = A.ID
LEFT JOIN ( SELECT A_ID, JSON_AGG(STUFF)       AS C_STUFF FROM C GROUP BY A_ID ) AS C_GROUPED ON C_GROUPED.A_ID = A.ID
LEFT JOIN ( SELECT A_ID, JSON_AGG(OTHER_STUFF) AS D_STUFF FROM D GROUP BY A_ID ) AS D_GROUPED ON D_GROUPED.A_ID = A.ID
WHERE A.ID = 123;

should return the same result as

SELECT 
    A.*, 
    B.*,
    ( SELECT JSON_AGG(STUFF)       FROM C WHERE C.A_ID = A.ID ) AS C_STUFF,
    ( SELECT JSON_AGG(OTHER_STUFF) FROM D WHERE D.A_ID = A.ID ) AS D_STUFF
FROM A
INNER JOIN B ON B.A_ID = A.ID
WHERE A.ID = 123

In fact, I would expect the second query be faster.

Ps - Since LEFT JOIN and LEFT OUTER JOIN are the same, I would suggest writing them in the same way in your query.

Upvotes: 1

Related Questions