PostgreSQL中WHERE语句筛选JSON列数据报错的解决方法

问题分析 #

原SQL报错operator is not unique: unknown ->> unknown的核心原因：

1. 未指定要操作的目标JSON列，直接用字符串'person'调用->>操作符，PostgreSQL无法识别操作对象的类型，导致类型不匹配。
2. startedByAttendanceEntry是JSON数组类型，而非单个JSON对象，直接访问person键无效，必须先展开数组元素。

正确查询方案 #

方案1：兼容全版本的数组展开查询

SELECT "startedByAttendanceEntry"
FROM "attendance_block" ab
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(ab."startedByAttendanceEntry") AS elem
    WHERE elem->'person'->>'firstName' = 'Koste'
      AND elem->'person'->>'lastName' = 'Litaci'
);

• 逻辑：通过json_array_elements将JSON数组拆分为多行元素，再检查是否存在符合条件的person对象，确保原表记录不会因数组多元素匹配而重复返回。

方案2：PostgreSQL 12+简洁写法

如果使用PostgreSQL 12及以上版本，可借助JSON包含操作符实现更简洁的查询：

SELECT "startedByAttendanceEntry"
FROM "attendance_block"
WHERE "startedByAttendanceEntry" @> '[{"person": {"firstName": "Koste", "lastName": "Litaci"}}]'::json;

• 逻辑：@>操作符用于检查JSON数组是否包含指定的对象元素，需将查询条件封装为数组形式，与目标列的数组结构保持一致。

内容的提问来源于stack exchange，提问作者x-magix