如何在PostgreSQL的JSON数组多元素中检查指定值？

针对你在PostgreSQL 13.9中检查嵌套JSON数组是否包含指定值的需求，以下是几种可行的正确查询方法：

方法1：修正@>运算符的用法 #

你之前的SQL错误在于将PostgreSQL数组转为JSONB，而@>运算符要求右侧是JSONB格式的数组。同时注意字段名和表名的正确性：

SELECT *
FROM your_table
WHERE jdata->'Outer'->'Inner' @> '[{"Value_Name":"Value"}]'::jsonb;

说明：如果jdata字段本身是jsonb类型，可以直接使用；如果是json类型，需要先转为jsonb，比如jdata::jsonb->'Outer'->'Inner'。这种方法适合创建了jsonb索引的场景，查询效率更高。

方法2：展开数组后匹配 #

通过jsonb_array_elements将嵌套数组展开，再匹配指定键值对：

SELECT DISTINCT t.*
FROM your_table t
JOIN jsonb_array_elements(t.jdata->'Outer'->'Inner') arr 
  ON arr->>'Value_Name' = 'Value';

说明：使用DISTINCT避免因为数组中有多个匹配元素导致同一行数据被多次返回。

方法3：使用EXISTS子查询 #

通过子查询判断是否存在匹配的数组元素，逻辑更清晰：

SELECT *
FROM your_table t
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(t.jdata->'Outer'->'Inner') arr
    WHERE arr->>'Value_Name' = 'Value'
);

方法4：使用JSONPath查询（PostgreSQL 12+支持） #

利用JSONPath语法实现更灵活的嵌套查询：

SELECT *
FROM your_table
WHERE jsonb_path_exists(
    jdata,
    '$.Outer.Inner[*] ? (@.Value_Name == "Value")'
);

说明：JSONPath语法支持更复杂的条件，比如多键匹配、范围判断等，适合复杂JSON结构的查询场景。

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