How to get flat aggregation of two calls to json_agg

Question

I have the following tables:

products

 id |    name     
----+-------------
  1 | Shampoo
  2 | Conditioner

productOptions

 id |    name     | productId 
----+-------------+-----------
  1 | Hair Growth |         1
  2 | Frizzy Hair |         1

images

 id | fileName  | productOptionId 
----+-----------+-----------------
  1 | bee.png   |               1
  2 | fancy.png |               2
  3 | soap.png  |               2

products have many productOptions, and productOptions have many images.

Following from this question, I have aggregated images.fileName twice to get an aggregated list of the fileNames for each product:

SELECT p.name, o.options, o.images
FROM products p
LEFT JOIN (
    SELECT "productId", array_agg(name) AS options, json_agg(i.images) AS images
    FROM "productOptions" o
    LEFT JOIN (
        SELECT "productOptionId", json_agg(i."fileName") AS images
        FROM images i
        GROUP BY 1
    ) i ON i."productOptionId" = o.id
    GROUP BY 1
) o ON o."productId" = p.id;

    name     |            options            |                  images                  
-------------+-------------------------------+------------------------------------------
 Shampoo     | {"Hair Growth","Frizzy Hair"} | [["bee.png"], ["fancy.png", "soap.png"]]
 Conditioner |                               |

I am wondering how to flatten the second json_agg so that the list of images is flat, and if my overall approach makes sense.

Please provide expected output – Rahul Biswas Sep 23 '21 at 07:35 — Rahul Biswas, Sep 23 '21 at 07:35

score 0 · Accepted Answer · answered Sep 23 '21 at 08:10

I didn't have to json_agg inside the inner-most JOIN, instead I can call array_agg(i.images) at the same point array_agg(name) AS options is called, to get a flat list of images:

SELECT p.name, o.options, o.images
FROM products p
LEFT JOIN (
    SELECT "productId", array_agg(DISTINCT name) AS options, array_agg(i.images) AS images
    FROM options o
    LEFT JOIN (
        SELECT "optionId", i."fileName" AS images
        FROM images i
    ) i ON i."optionId" = o.id
    GROUP BY 1
) o ON o."productId" = p.id;

    name     |            options            |            images            
-------------+-------------------------------+------------------------------
 Shampoo     | {"Frizzy Hair","Hair Growth"} | {bee.png,fancy.png,soap.png}
 Conditioner |

nachospiu · Answer 2 · 2021-09-24T10:37:47.800

A different approach:

I used DISTINCT in function json_agg() (you can use array_agg() instead), so as not to repeat the name of the product Options.

SELECT 
      p.name, 
      json_agg(DISTINCT po.name) AS options,
      json_agg(i."fileName") AS images
      
FROM products p
LEFT JOIN "productOptions" po ON p.id = po."productId"
LEFT JOIN images AS i ON po.id = i."productOptionId"
GROUP BY p.name;

Or using subqueries:

SELECT 
      p.name, 
      po.options,
      poi.images
      
FROM products p

LEFT JOIN (SELECT productId, json_agg(name) AS options
           FROM productOptions
           GROUP BY productId) AS po ON p.id = po.productId
           
LEFT JOIN (SELECT 
                  productId, 
                  json_agg(fileName) AS images 
            FROM productOptions po
            INNER JOIN images i ON i.productOptionId = po.id
            GROUP BY productId) AS poi ON p.id = poi.productId;

I was reading this answer, https://stackoverflow.com/a/27626358/3171685. I was trying to use the subqueries to avoid multiplying rows, not sure which of our answers is more performant. — jsindos, Sep 24 '21 at 00:58
Yes, I am not sure. You should try both and chose the best one (if your tables don't have many rows, you might not notice a significant difference). — nachospiu, Sep 24 '21 at 10:51

How to get flat aggregation of two calls to json_agg

2 Answers2