基于RECEIVERS.FIRST_NAME查询时返回同ID全关联数据的SQL JSON问题

问题核心是不能直接在WHERE子句里过滤收件人姓名，否则会把同订单下的其他收件人直接排除。正确逻辑是先定位到包含指定收件人的所有订单，再关联这些订单的全部收件人和商品数据。

具体SQL示例（以PostgreSQL为例） #

-- 先筛选出包含指定收件人的所有订单ID
WITH target_order_ids AS (
    SELECT DISTINCT id 
    FROM RECEIVERS 
    WHERE FIRST_NAME = 'Pete'
)
-- 关联订单、所有收件人、所有商品，聚合为嵌套JSON结构
SELECT
    o.id AS order_id,
    -- 实际生产建议明确列出ORDER表字段，避免使用o.*
    o.order_number,
    o.order_date,
    JSON_AGG(DISTINCT r) AS receivers,
    JSON_AGG(DISTINCT p) AS products
FROM "ORDER" o
JOIN target_order_ids toi ON o.id = toi.id
LEFT JOIN RECEIVERS r ON o.id = r.id
LEFT JOIN PRODUCTS p ON o.id = p.id
GROUP BY o.id, o.order_number, o.order_date;

关键说明 #

1. 先锁定目标订单：通过target_order_ids临时表拿到所有关联了"Pete"的订单ID，这一步保证不会提前过滤掉订单的其他关联数据。
2. 全量关联数据：用LEFT JOIN关联RECEIVERS和PRODUCTS（如果存在无收件人/商品的订单场景），确保每个订单下的所有收件人、商品都被纳入统计。
3. 聚合生成嵌套JSON：借助JSON_AGG函数把同订单下的所有收件人、商品分别聚合成JSON数组，最终形成需求的嵌套结构。

不同数据库适配调整 #

• MySQL：将JSON_AGG替换为JSON_ARRAYAGG，注意DISTINCT的使用方式需适配MySQL语法。
• Oracle：使用JSON_ARRAYAGG(JSON_OBJECT(*))生成JSON数组，同时GROUP BY子句必须包含所有非聚合字段。

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