如何简化PostgreSQL中基于jsonb多键匹配的WHERE子句查询？

假设你有如下表格（tags列为jsonb类型，所有键均为顶级字符串键）：

要筛选出tags同时包含多个指定顶级键的行，不需要重复写多个AND条件，有以下几种更高效简洁的实现方式：

1. 使用?&运算符（PostgreSQL 9.5+ 支持） #

?&是PostgreSQL专门为jsonb设计的运算符，用于判断jsonb对象是否包含数组中的所有指定键，写法极为简洁：

SELECT * 
FROM your_table 
WHERE tags ?& ARRAY['color', 'role'];

如果需要匹配更多键，只需在数组中追加元素即可，比如要同时匹配color、role、zone，只需写成ARRAY['color', 'role', 'zone']。

2. 使用jsonb_has_all_keys函数（PostgreSQL 12+ 支持） #

这个函数的语义更直观，明确用于检查jsonb对象是否包含所有传入的键：

SELECT * 
FROM your_table 
WHERE jsonb_has_all_keys(tags, ARRAY['color', 'role']);

和?&运算符逻辑一致，只是函数形式的写法，可读性更强。

兼容低版本的聚合查询方式（PostgreSQL <9.5） #

如果你的PostgreSQL版本低于9.5，无法使用上述运算符/函数，可以通过聚合查询间接实现：

SELECT t.*
FROM your_table t
JOIN (
    SELECT name
    FROM your_table, jsonb_object_keys(tags) AS key
    WHERE key IN ('color', 'role')
    GROUP BY name
    HAVING COUNT(DISTINCT key) = 2 -- 此处数字需与要匹配的键数量一致
) sub ON t.name = sub.name;

不过这种写法的执行效率不如前两种方法，优先推荐使用运算符或函数方案。

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