PostgreSQL中如何在jsonb列中使用位运算符查询数据

表结构示例 #

id | access
--------------------------
1  | [{"id": 1, "user":"user1", "permission": 1}, {"id": 2, "user":"user2", "permission": 3}]
2  | [{"id": 1, "user":"user1", "permission": 3}, {"id": 2, "user":"user2", "permission": 7}]

需求说明 #

需要筛选出access数组中存在user为"user1"且permission的位2权限已开启（即满足permission & 2 = 2）的记录，示例中应返回id为2的记录。

当前已实现用户过滤，但无法处理权限位判断的查询语句：

SELECT * FROM my_table WHERE jsonb_path_exists("access", '$[*] ? (@.user == "user1")')

权限位编码规则 #

权限采用位编码，对应关系如下：

• 1 -> 001
• 2 -> 010
• 3 -> 011
• 4 -> 100
• 5 -> 101
• 6 -> 110

解决方案 #

方案1：扩展JSON路径表达式

直接在jsonb_path_exists的路径条件中加入位运算判断，一次性完成用户和权限的过滤：

SELECT * FROM my_table 
WHERE jsonb_path_exists(
  "access", 
  '$[*] ? (@.user == "user1" && (@.permission & 2) == 2)'
);

方案2：展开JSON数组后过滤

通过jsonb_array_elements将数组展开为行，用原生SQL的位运算判断权限，最后去重返回原表记录：

SELECT DISTINCT t.* 
FROM my_table t
JOIN jsonb_array_elements(t.access) AS elem ON true
WHERE elem->>'user' = 'user1' 
  AND (elem->>'permission')::integer & 2 = 2;

两种方案均可实现需求：方案1语法简洁，适合简单场景；方案2利用原生SQL运算，在数据量较大时若有合适索引，性能表现更优。

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