PostgreSQL 13.4如何对含数组的JSON字段实现IN过滤与排序

你写的预期逻辑存在问题：details->>'code'只能读取JSON对象顶层的code字段，没法直接取到locations数组里嵌套对象的code属性，以下是两种可直接使用的正确实现：

方式1：数组展开匹配（兼容性强） #

SELECT DISTINCT t.*
FROM 替换为你的实际表名 t,
     json_array_elements(t.details -> 'locations') AS loc
WHERE loc ->> 'code' IN ('LK','UK')
ORDER BY t.details ->> 'email';

• 逻辑说明：通过json_array_elements把locations数组里的每一个元素拆成独立行和主表关联，再对单个location对象的code属性做匹配；加DISTINCT是为了避免同一条记录同时命中多个code值时返回重复结果。
• 排序逻辑和你预期一致：details ->> 'email'直接取JSON顶层的email字段即可，这个写法本身是正确的。

方式2：JSON路径匹配（PG12+支持，写法更简洁性能更好） #

PostgreSQL 13完全支持JSONPath语法，不需要拆数组就能直接判断数组内是否存在符合条件的元素，不会产生重复行：

SELECT *
FROM 替换为你的实际表名
WHERE details @? '$.locations[*] ? (@.code in ("LK", "UK"))'
ORDER BY details ->> 'email';

• 逻辑说明：@?操作符用来校验JSON数据是否匹配指定路径规则，路径里$.locations[*]遍历locations下所有数组元素，筛选出code值属于目标列表的记录即可。

补充说明：如果你的details字段是jsonb类型而非json类型，只需要把方式1里的json_array_elements替换为jsonb_array_elements即可，方式2的路径查询写法对json和jsonb类型都兼容。

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