How to get flat aggregation of two calls to json

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.

CodePudding user response：

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 |

CodePudding user response：

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;