PostgreSQL 12.3：查询JSON字段满足指定条件的表记录

针对你的需求，这里提供两种场景的SQL查询方案：

基础场景：仅筛选persons数组中存在符合条件元素的记录 #

如果只需要找出persons数组里至少有一个元素满足test.id = '123'或test为null的记录，可以用以下SQL：

SELECT *
FROM aaa
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(data->'persons') AS p
    WHERE (p->'test'->>'id' = '123') OR (p->'test' IS NULL)
);

代码说明： #

• json_array_elements(data->'persons')：将data列中的personsJSON数组拆分为单行记录，p代表数组中的每个元素。
• p->'test'->>'id' = '123'：提取数组元素中test对象的id字段（转为文本类型），判断是否等于目标值'123'。
• p->'test' IS NULL：检查数组元素的test字段是否为null。
• EXISTS子查询：只要数组内存在任意一个满足上述条件的元素，就返回对应的表记录。

扩展场景：包含persons数组为空/不存在的记录 #

如果需要同时包含persons字段不存在、为null或为空数组的记录，可以调整查询条件：

SELECT *
FROM aaa
WHERE 
    EXISTS (
        SELECT 1
        FROM json_array_elements(data->'persons') AS p
        WHERE (p->'test'->>'id' = '123') OR (p->'test' IS NULL)
    )
    OR (data->'persons' IS NULL OR json_array_length(data->'persons') = 0);

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