如何检测PostgreSQL的jsonb列中是否存在键值对重叠？

问题回顾 #

你已经通过<@操作符实现了jsonb列内容是否为查询参数的子集的判断：

SELECT '{"k1": "v1"}'::jsonb <@ '{"k1": "v1", "k2":"v2"}'::jsonb;  -- 返回 true

现在需要实现两个jsonb是否存在键值对重叠的判断，之前尝试&&操作符报错，因为PostgreSQL未为jsonb定义该操作符。

解决方案 #

方法1：使用jsonb_intersection函数（PostgreSQL 12+） #

PostgreSQL 12及以上版本提供了jsonb_intersection函数，可直接返回两个jsonb的键值对交集，再判断结果是否非空即可：

SELECT jsonb_intersection('{"k1": "v1", "k3":"v4"}'::jsonb, '{"k1": "v1", "k2":"v2"}'::jsonb) <> '{}'::jsonb;
-- 返回 true，因为交集是 {"k1": "v1"}

方法2：兼容低版本的自定义判断 #

如果你的PostgreSQL版本低于12，可以通过提取键值对后对比的方式实现：

SELECT EXISTS (
    SELECT 1
    FROM jsonb_each('{"k1": "v1", "k3":"v4"}'::jsonb) AS a(k, v)
    JOIN jsonb_each('{"k1": "v1", "k2":"v2"}'::jsonb) AS b(k, v)
    ON a.k = b.k AND a.v = b.v
);
-- 返回 true

该查询将两个jsonb展开为键值对行，匹配相同的键和值，只要存在匹配行就返回true。

内容的提问来源于stack exchange，提问作者Debasish Mitra