如何在所有值为null时返回空数组？PostgreSQL JSON聚合问题

你的问题根源在于多表LEFT JOIN产生的笛卡尔积，以及错误的GROUP BY分组逻辑：

• 当你同时LEFT JOIN多个关联表（如product_tags、product_variants等）时，这些表的行数会互相相乘生成笛卡尔积。比如某商品有3个标签、1个变体，即使没有媒体数据，也会生成3行记录，每行的pm字段都是null，最终json_agg会把这3个null对象聚合到数组里。
• 你的GROUP BY包含了pm.id、po.id等关联表的ID，这会让每个关联表的行单独分组，而非按商品主键聚合，进一步加剧了冗余问题。

最优方案是对每个需要聚合的关联表先做子查询聚合，再与主表JOIN，从根源避免笛卡尔积，同时确保无数据时返回空数组。

优化后的查询代码 #

SELECT 
  p.id,
  p.shop_id,
  p.provider_id,
  p.name,
  p.status,
  -- 无标签时返回空数组
  COALESCE(t.tags, '[]'::json) AS tags,
  -- 无媒体数据时返回空数组
  COALESCE(m.product_media, '[]'::json) AS product_media,
  -- 聚合商品选项及选项值
  COALESCE(o.product_options, '[]'::json) AS product_options,
  -- 聚合商品变体
  COALESCE(v.product_variants, '[]'::json) AS product_variants
FROM product p
-- 聚合标签
LEFT JOIN (
  SELECT 
    pt.p_id,
    json_agg(json_build_object('id', pt.id, 'tag', pt.tag)) AS tags
  FROM product_tags pt
  GROUP BY pt.p_id
) t ON t.p_id = p.id
-- 聚合媒体数据
LEFT JOIN (
  SELECT 
    pm.p_id,
    json_agg(json_build_object(
      'id', pm.id,
      'src', pm.src,
      'alt', pm.alt,
      's_position', pm.s_position,
      'updated_at', pm.updated_at
    )) AS product_media
  FROM product_media pm
  GROUP BY pm.p_id
) m ON m.p_id = p.id
-- 聚合选项及选项值
LEFT JOIN (
  SELECT 
    po.p_id,
    json_agg(json_build_object(
      'id', po.id,
      'name', po.name,
      'position', po.position,
      'values', COALESCE(pov.values, '[]'::json)
    )) AS product_options
  FROM product_options po
  LEFT JOIN (
    SELECT 
      pov.p_o_id,
      json_agg(json_build_object('id', pov.id, 'value', pov.value)) AS values
    FROM product_options_value pov
    GROUP BY pov.p_o_id
  ) pov ON pov.p_o_id = po.id
  GROUP BY po.p_id
) o ON o.p_id = p.id
-- 聚合变体
LEFT JOIN (
  SELECT 
    pv.p_id,
    json_agg(json_build_object(
      'id', pv.id,
      'quantity', pv.quantity,
      'price', pv.price,
      'is_male', pv.is_male,
      'shipping_price', pv.shipping_price,
      'grams', pv.grams,
      'weight', pv.weight,
      'weight_unit', pv.weight_unit,
      'length', pv.length,
      's_position', pv.s_position,
      'option_1', pv.option_1,
      'option_2', pv.option_2,
      'option_3', pv.option_3
    )) AS product_variants
  FROM product_variants pv
  GROUP BY pv.p_id
) v ON v.p_id = p.id
WHERE p.provider_id = $1
LIMIT 1;

关键优化点 #

1. 子查询预聚合：每个关联表先单独按p_id聚合生成JSON数组，再与主表JOIN，彻底避免笛卡尔积导致的冗余null对象。
2. COALESCE处理空值：用COALESCE(..., '[]'::json)确保关联表无数据时返回空数组，而非null或冗余null对象数组。
3. 正确分组逻辑：仅按主表product.id分组，确保每个商品只返回一行聚合结果。

简化版（适合快速修复） #

如果不想重构子查询，也可以通过FILTER过滤null行+修正GROUP BY来临时解决，但需注意笛卡尔积仍可能导致重复数据，需用DISTINCT去重：

SELECT 
  p.id,
  p.shop_id,
  p.provider_id,
  p.name,
  p.status,
  COALESCE(json_agg(DISTINCT json_build_object('id', pt.id, 'tag', pt.tag)) FILTER (WHERE pt.id IS NOT NULL), '[]'::json) AS tags,
  COALESCE(json_agg(DISTINCT json_build_object(
    'id', pm.id,
    'src', pm.src,
    'alt', pm.alt,
    's_position', pm.s_position,
    'updated_at', pm.updated_at
  )) FILTER (WHERE pm.id IS NOT NULL), '[]'::json) AS product_media
FROM product p
LEFT JOIN product_media pm ON pm.p_id = p.id
LEFT JOIN product_tags pt ON pt.p_id = p.id
LEFT JOIN product_options po ON po.p_id = p.id
LEFT JOIN product_options_value pov ON pov.p_o_id = po.id
LEFT JOIN product_variants pv ON pv.p_id = p.id
WHERE p.provider_id = $1
GROUP BY p.id
LIMIT 1;

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