PostgreSQL JSONB数组对象交集查询的技术实现问题

我的PostgreSQL数据库中部分行包含一个JSONB类型列，该列存储的对象数组格式如下：

[
  {"type": "human", "name": "Alice"},
  {"type": "dog", "name": "Fido"},
  {"type": "dog", "name": "Pluto"}
]

我需要编写查询语句，判断传入的数组参数与该列数组是否存在交集（即任一对象完全匹配）：

• 若搜索参数为[{"type": "human", "name": "Alice"}]，应命中目标行；
• 若搜索参数为[{"type": "human", "name": "Alice"}, {"type": "dog", "name": "Doggy"}]，也应命中目标行（因其中一个对象匹配）。

我曾尝试使用?|运算符，但该运算符仅按键进行比较，无法满足完整JSONB对象匹配的需求。

解决方案 #

方法一：使用&&运算符（PostgreSQL 12+）

PostgreSQL 12及以上版本支持JSONB数组的&&重叠运算符，可直接判断两个数组是否存在完全匹配的元素：

SELECT *
FROM your_table
WHERE your_jsonb_column && '[{"type": "human", "name": "Alice"}, {"type": "dog", "name": "Doggy"}]'::jsonb;

该运算符会检查列中的JSONB数组与传入的参数数组是否有至少一个完全相同的JSONB对象，符合交集判断需求。

方法二：兼容低版本的EXISTS子查询（PostgreSQL <12）

如果你的PostgreSQL版本低于12，可以通过展开数组的方式实现：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(your_jsonb_column) AS col_elem
    JOIN jsonb_array_elements('[{"type": "human", "name": "Alice"}, {"type": "dog", "name": "Doggy"}]'::jsonb) AS param_elem
    ON col_elem = param_elem
);

这个查询会将列中的JSONB数组和参数数组分别展开，通过匹配相等元素来判断交集是否存在。

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