SQL查询中如何提取c.<type>.<type>嵌套数据并保留嵌套结构避免重复

解决方案 #

你可以使用ARRAY构造器搭配子查询实现需求，无需通过JOIN展开嵌套数组导致顶层字段重复，参考写法如下：

SELECT 
    c.receipt_number, 
    c.millitime, 
    c.email, 
    c.phone, 
    c.shipping, 
    c.shipping_note, 
    c.amount_paid,
    ARRAY(
        SELECT i.description, i.quantity 
        FROM i IN c.line_items
    ) AS line_items
FROM c
WHERE c.millitime > 1627813253000

原理解释 #

• 原写法使用JOIN会把line_items数组的每个元素和顶层文档字段做笛卡尔积，每个子项都会生成一条独立的顶层记录，因此会出现大量重复的收据信息字段。
• 上述写法中的ARRAY构造器会将子查询的返回结果拼接为数组，直接对每条文档的line_items数组做字段投影，仅保留需要的description和quantity字段，最终返回的line_items会保留嵌套结构，不会展开为多条顶层记录，完全匹配预期的返回格式。

如果需要对line_items的子项做过滤，直接在子查询中添加WHERE条件即可，比如仅保留数量大于0的子项：

ARRAY(
    SELECT i.description, i.quantity 
    FROM i IN c.line_items
    WHERE i.quantity > 0
) AS line_items

内容的提问来源于stack exchange，提问作者Lola