PSQL如何筛选JSON对象数组中存在指定id属性值的所有行

方案1：使用@>包含运算符（推荐，性能最优） #

如果你的args字段是jsonb类型（PostgreSQL推荐优先使用jsonb类型存储JSON数据），可以直接用包含运算符判断，语法最简单且执行效率最高，如果你已对args字段建立GIN索引，查询速度会非常快：

SELECT * FROM 你的表名 
WHERE args @> '{"args":{"args_json":{"actions":[{"id":79}]}}}'::jsonb;

这个语法的逻辑是直接判断JSON结构中是否存在匹配的嵌套数组元素，只要actions数组里有任意一个对象包含id:79的属性就会命中。

方案2：展开数组做存在性校验（适合复杂过滤场景） #

如果你的字段是json类型，或者需要搭配更复杂的过滤规则，可以用EXISTS搭配数组展开函数实现：

SELECT * FROM 你的表名 t
WHERE EXISTS (
    SELECT 1
    -- 如果是json类型，将jsonb_array_elements替换为json_array_elements即可
    FROM jsonb_array_elements(t.args #> '{args_json, actions}') AS action_item
    WHERE action_item ->> 'id' = '79'
);

这个方案的逻辑是把每行的actions数组拆分为单个对象行，只要有任意一行满足id=79的条件，EXISTS就会返回真，对应的原表行就会被查询出来。

内容的提问来源于stack exchange，提问作者François Richard