PostgreSQL一对多转JSON结果后如何实现过滤？

嘿，这个场景我之前也碰到过！核心是千万别直接在聚合地址前过滤匹配的街道——那样会把客户的其他非匹配地址给丢掉。正确的思路是先锁定至少有一个地址符合搜索条件的客户，再把这些客户的所有地址完整聚合为JSON数组，下面给你分主流数据库的具体实现方案：

先通过子查询/EXISTS找到所有存在匹配街道地址的客户ID，再基于这些ID关联地址表进行聚合，确保最终返回的是客户的全部地址，同时满足“至少有一个地址符合搜索词”的筛选条件。

1. PostgreSQL 实现 #

假设你的表结构是：

• customers（id, name）：客户主表
• addresses（id, customer_id, street_name, city）：地址表，关联客户ID

方式一：用IN子查询筛选客户 #

SELECT 
  c.id,
  c.name,
  json_agg(
    json_build_object(
      'street_name', a.street_name,
      'city', a.city
    )
  ) AS addresses
FROM customers c
JOIN addresses a ON c.id = a.customer_id
WHERE c.id IN (
  -- 先找出所有有匹配街道的客户ID
  SELECT DISTINCT customer_id
  FROM addresses
  WHERE street_name ILIKE '%你的搜索词%' -- ILIKE不区分大小写，用LIKE则区分
)
GROUP BY c.id, c.name;

方式二：用EXISTS子查询（更高效） #

数据量大的时候，EXISTS的性能通常比IN更好，因为它找到匹配项就会停止检索：

SELECT 
  c.id,
  c.name,
  json_agg(
    json_build_object(
      'street_name', a.street_name,
      'city', a.city
    )
  ) AS addresses
FROM customers c
JOIN addresses a ON c.id = a.customer_id
WHERE EXISTS (
  SELECT 1
  FROM addresses a2
  WHERE a2.customer_id = c.id
    AND a2.street_name ILIKE '%你的搜索词%'
)
GROUP BY c.id, c.name;

2. MySQL 实现 #

MySQL 5.7及以上版本支持JSON聚合函数，用JSON_ARRAYAGG生成JSON数组：

SELECT 
  c.id,
  c.name,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'street_name', a.street_name,
      'city', a.city
    )
  ) AS addresses
FROM customers c
JOIN addresses a ON c.id = a.customer_id
WHERE c.id IN (
  SELECT DISTINCT customer_id
  FROM addresses
  WHERE street_name LIKE '%你的搜索词%' -- 若需不区分大小写，确保表/字段排序规则为utf8mb4_unicode_ci
)
GROUP BY c.id, c.name;

同样可以把IN替换为EXISTS，逻辑和PostgreSQL一致，性能更优。

3. SQL Server 实现 #

SQL Server用FOR JSON PATH直接生成客户的所有地址JSON数组，语法更简洁：

SELECT 
  c.id,
  c.name,
  (
    -- 子查询生成当前客户的全部地址JSON数组
    SELECT street_name, city
    FROM addresses a
    WHERE a.customer_id = c.id
    FOR JSON PATH
  ) AS addresses
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM addresses a2
  WHERE a2.customer_id = c.id
    AND a2.street_name LIKE '%你的搜索词%'
);

这里不需要GROUP BY，因为子查询已经完成了地址聚合。

• 模糊匹配优化：如果搜索词用%前缀%的形式，会导致索引失效，数据量大时建议使用全文索引来提升性能。
• 空地址处理：如果客户可能存在无地址的情况，但你的需求是筛选有匹配地址的客户，所以用JOIN即可；若要保留无地址的客户（虽然不符合筛选条件），可以改用LEFT JOIN，但EXISTS会确保至少有一个匹配地址。
• 索引优化：给addresses表的customer_id和street_name建立联合索引，能大幅提升子查询的筛选速度。

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