PostgreSQL使用动态CASE WHEN实现会话数据按页面权限过滤查询

核心过滤逻辑如下：

• 当page_id = 1时，直接保留该页面下所有条目
• 当page_id = 2时，仅保留assigned_user_ids字段中包含当前查询用户（即user1）的条目

修改后的SQL语句（以MySQL为例，适配JSON类型的assigned_user_ids字段） #

SELECT * FROM Conversations
WHERE PageId IN (1, 2)
AND CASE
    WHEN PageId = 1 THEN 1 = 1
    WHEN PageId = 2 THEN JSON_CONTAINS(assigned_user_ids, '"user1"') = 1
    ELSE 0 = 1
END

实际使用时可以将"user1"替换为动态参数，适配不同查询用户的权限逻辑。

逻辑说明 #

CASE WHEN会逐行判断当前记录的PageId值，返回对应的布尔判断结果：

• 若PageId = 1，直接返回true，保留当前page_id=1的所有记录
• 若PageId = 2，调用JSON_CONTAINS函数判断assigned_user_ids数组中是否包含当前查询用户，仅包含时返回true保留记录
• 其余情况默认返回false，过滤掉无关记录

不同数据库的适配调整 #

如果使用其他数据库，只需要调整数组包含判断的语法即可：

• PostgreSQL（assigned_user_ids为数组类型）：'user1' = ANY(assigned_user_ids)
• PostgreSQL（assigned_user_ids为JSONB类型）：assigned_user_ids @> '["user1"]'::jsonb
• SQL Server：EXISTS(SELECT 1 FROM OPENJSON(assigned_user_ids) WHERE value = 'user1')

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