在PostgreSQL中使用IN运算符过滤JSON数据的方法

假设你的表名为your_table，存储JSON数据的列为json_col，现有JSON数据如下：

{"List": [{"f1": "eed31964","f2": "ABC"},{"f1": "964433d1","f2": "DEF"}]}

你需要筛选出JSON数组List中**存在至少一个对象满足f1 IN ('eed31964','964433d1') 且 f2 IN ('ABC','DEF')**的行，以下是两种可行方案：

方案1：使用数组展开+EXISTS子查询 #

通过json_array_elements（若为jsonb类型则用jsonb_array_elements）将JSON数组展开为行，再通过EXISTS子查询判断是否存在符合条件的对象：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(json_col->'List') AS elem
    WHERE (elem->>'f1') IN ('eed31964','964433d1')
      AND (elem->>'f2') IN ('ABC','DEF')
);

方案2：使用JSON路径表达式（PostgreSQL 12+） #

利用PostgreSQL 12及以上版本支持的JSON路径语法，直接通过@?操作符判断数组中是否存在符合条件的元素：

SELECT *
FROM your_table
WHERE json_col @? '$.List[*] ? (@.f1 IN ("eed31964", "964433d1") && @.f2 IN ("ABC", "DEF"))';

关于你之前尝试的问题 #

你之前用的column->'List' @> '[{"f1": "eed31964"},{"f1": "964433d1"}]'是PostgreSQL的JSON包含操作符，它要求目标数组同时包含所有指定的对象（即数组里必须既有f1=eed31964的对象，又有f1=964433d1的对象），属于AND逻辑，和你需要的"存在单个对象满足f1和f2分别在指定集合"的需求不符，所以需要用上述两种方法替代。

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