在Postgres中，检查JSONB列中的任何数组元素是否存在于另一个数组中。

Question

我正在使用PostgreSQL 13.9，想要检查JSONB中的数组与另一个输入数组是否有任何元素重叠，如果存在，则在以下示例SQL中返回true：

select 
  '{
  "id1": ["5"],  
  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   
  "id5": "11",
  "id6": "2",  
  "id7": ["12","18"]
}'::jsonb @? '$ ? ((@.id2[*] ?| ["5","7"]))'::jsonpath

但是遇到错误：

ERROR:  syntax error at or near "|" of jsonpath
LINE 7: }'::jsonb @? '$ ? ((@.id2[*] ?| ["5","7"]))'::jsonpath
^
SQL state: 42601
Character: 153

当我用单个元素进行比较时，如下所示，它能够正常工作并返回"true"：

select 
  '{
  "id1": ["9"],  
  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   
  "id5": "11",
  "id6": "2",  
  "id7": ["12","18"]
}'::jsonb @? '$ ? ((@.id1 == "9") && (@.id2[*] == "5"))'::jsonpath

我想以高性能的方式实现此操作，因此不想使用多个OR条件来编写查询，例如：

(@.id2[*]== "5" || @.id2[*] == "7")

关于我在哪里出错，如果有任何提示将非常有帮助。

wim · Answer

快速且简化的答案，使用来自PostgreSQL文档中“JSON函数/运算符”部分的jsonb_path_query函数（参见表9.49：JSON处理函数）：

-- 查询示例1：检查"id2"数组是否包含'5'或'7'
SELECT jsonb_path_query(
  '{
    "id1": ["5"],
    "id2": ["5"],
    "id3": ["cc","dd"],
    "id4": "15",
    "id5": "11",
    "id6": "2",
    "id7": ["12","18"]
  }'::jsonb,
  '$.id2') ?| array['5','7'];

-- 结果：t （表示true，即'id2'数组包含'5'）

-- 查询示例2：检查"id3"数组是否包含'5'或'7'
SELECT jsonb_path_query(
  '{
    "id1": ["5"],
    "id2": ["5"],
    "id3": ["cc","dd"],
    "id4": "15",
    "id5": "11",
    "id6": "2",
    "id7": ["12","18"]
  }'::jsonb,
  '$.id3') ?| array['5','7'];

-- 结果：f （表示false，即'id3'数组不包含'5'或'7'）

Gray · Answer

正如已经指出的那样，你尝试使用的?|是纯PostgreSQL中的一个运算符，但在当前实现的JSONPath中并不支持，因此不能在这种表达式中使用它。此外，在表达式中也不能使用数组或对象字面量，所以["5","7"]也无法在此处使用。

如果你有一个jsonb对象，并想要与另一个对象进行比较，你可以将整个对象作为变量传递到JSONPath表达式中。@?操作符不允许这样做，但jsonb_path_exists()函数可以做到这一点。一旦两个对象都在JSONPath表达式中，它们都可以使用[*]访问器：

select jsonb_path_exists(
  '{"id1": ["5"],  "id2": ["5"],
   "id3": ["cc","dd"],
   "id4": "15",   "id5": "11",
   "id6": "2",  "id7": ["12","18"]
  }'::jsonb,
  '$.id2[*]?(@==$var.id2[*])',
  '{"var":{"id2":["5","7"]}}'::jsonb
);

结果显示为 t，表示存在匹配项。

或者，你也可以直接将其合并到原始jsonb对象中：

select jsonb_path_exists(
  '{"id1": ["5"],  "id2": ["5"],
   "id3": ["cc","dd"],
   "id4": "15",   "id5": "11",
   "id6": "2",  "id7": ["12","18"]
  }'::jsonb || '{"var":{"id2":["5","7"]}}'::jsonb,
  '$.id2[*]?(@==$var.id2[*])'
);

同样得到结果为 t。

另外，如果你只想检查某个键值对应的数组是否包含特定的多个元素，可以使用常规的->操作符获取该键，并直接结合?|操作符以及text[]数组：

select 
  '  {
  "id1": ["5"],  "id2": ["5"],
  "id3": ["cc","dd"],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb -> 'id2' ?| '{5,7}'

结果显示为 t，表示"id2"数组包含了'5'或'7'。

在线演示示例

Robert Rossney · Answer

关于以下SQL查询：

select exists (
  select from jsonb_array_elements('[1, 2, 3]'::jsonb)
  where value::int = any(array[1, 4])
);