将Supabase关联查询转换为PostgreSQL原生SQL的方法

针对store与product的一对多关系，要实现类似Supabase的嵌套结果（每个商店对应一个商品列表，无重复商店数据），可以用PostgreSQL的聚合函数替代普通JOIN，以下是两种常用写法：

1. 返回JSON格式的商品名称列表 #

适合需要结构化嵌套数据的场景：

SELECT 
  s.id,
  s.name,
  -- 聚合商品名称为JSON数组，无商品时返回空数组
  COALESCE(
    json_agg(p.name) FILTER (WHERE p.id IS NOT NULL),
    '[]'::json
  ) AS products
FROM store s
LEFT JOIN product p ON s.id = p.store_id
GROUP BY s.id, s.name;

2. 返回数组格式的商品名称 #

如果只需要纯字符串数组，用array_agg更轻量：

SELECT 
  s.id,
  s.name,
  -- 聚合商品名称为字符串数组，无商品时返回空数组
  COALESCE(
    array_agg(p.name) FILTER (WHERE p.id IS NOT NULL),
    '{}'::text[]
  ) AS product_names
FROM store s
LEFT JOIN product p ON s.id = p.store_id
GROUP BY s.id, s.name;

关键说明 #

• LEFT JOIN保证没有商品的商店也会被查询到
• FILTER (WHERE p.id IS NOT NULL)过滤掉无商品时产生的null值，配合COALESCE返回空数组
• 由于id是store表主键，GROUP BY s.id即可，加上s.name是为了兼容部分SQL模式，实际可省略s.name

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