PostgreSQL：如何查询CTE返回的JSON对象数组中指定字段值的条目？

你需要检查JSON数组中是否存在符合条件的元素，直接用owners->>'name'是错误的——因为owners是JSON数组，不是单个对象，这个写法会尝试读取数组本身的name属性（显然不存在）。以下是两种可行的解决方案：

方案1：使用JSON包含操作符（推荐，性能更优） #

如果可以将聚合结果改为jsonb_agg（或者将现有json类型转为jsonb），可以用@>操作符直接判断数组是否包含指定对象：

WITH results AS (
  -- 保留你的其他字段
  (SELECT jsonb_agg(owners)
    FROM (
      SELECT id, name, telephone, email
      FROM owner
    ) owners   
  ) as owners
)
SELECT * 
FROM results 
WHERE owners @> '[{"name": "John Smith"}]';

如果必须保留json类型，只需在判断时转为jsonb：

WITH results AS (
  -- 保留你的其他字段
  (SELECT json_agg(owners)
    FROM (
      SELECT id, name, telephone, email
      FROM owner
    ) owners   
  ) as owners
)
SELECT * 
FROM results 
WHERE owners::jsonb @> '[{"name": "John Smith"}]';

方案2：使用EXISTS子查询展开数组检查 #

这种方式更灵活，适合需要对数组元素做复杂条件判断的场景：

WITH results AS (
  -- 保留你的其他字段
  (SELECT json_agg(owners)
    FROM (
      SELECT id, name, telephone, email
      FROM owner
    ) owners   
  ) as owners
)
SELECT r.* 
FROM results r
WHERE EXISTS (
  SELECT 1
  FROM json_array_elements(r.owners) AS o
  WHERE o->>'name' = 'John Smith'
);

说明 #

• @>操作符：语法简洁，性能出色，如果给owners字段创建jsonb索引，查询速度会大幅提升，适合简单的包含检查。
• EXISTS子查询：可以扩展支持多条件判断（比如同时验证name和telephone），适用性更广。

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