如何获得对json_agg的两个调用的平面聚合

Question

我有以下表格:

产品

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

productOptions

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

图片

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

products有许多productOptions,productOptions有许多images。

根据这个问题，我对images.fileName进行了两次聚合，以获得每个产品的fileNames的聚合列表:

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 |                               | 

我想知道如何平坦第二个json_agg，使图像列表是平坦的，如果我的整体方法是有意义的。

Answer 1

我不需要在最内部的JOIN中调用json_agg，相反，我可以在调用array_agg(name) AS options的同一点调用array_agg(i.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 | 

Answer 2

另一种方法:

我在函数json_agg()中使用了DISTINCT(您可以使用array_agg()代替)，以免重复产品选项的名称。

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;

或使用子查询:

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;