MongoDB权限集合关联组集合：解析组权限并排除重复用户

我有两个MongoDB集合：permissions和groups，数据如下：

permissions集合数据 #

[
    {
        "_id": 1,
        "resource": "resource:docs/61",
        "permissions": [
            {
                "permission": "role:documentOwner",
                "users": [
                    "user:def",
                    "group:abc"
                ]
            },
            {
                "permission": "document.read",
                "users": [
                    "user:abc",
                    "user:xxx",
                    "group:abc"
                ]
            },
            {
                "permission": "document.update",
                "users": [
                    "user:xxx"
                ]
            }
        ]
    },
    {
        "_id": 2,
        "resource": "resource:docs/38",
        "permissions": [
            {
                "permission": "role:documentOwner",
                "users": [
                    "user:abc",
                    "user:def",
                    "group:abc",
                    "group:bff"
                ]
            },
            {
                "permission": "document.read",
                "users": [
                    "user:xxx"
                ]
            },
            {
                "permission": "document.update",
                "users": [
                    "user:xxx"
                ]
            }
        ]
    }
]

groups集合数据 #

[
    {
        "_id": 1,
        "id": "abc",
        "name": "Test Group",
        "users": [
            "abc",
            "cpo",
            "yyy",
            "xxx"
        ]
    },
    {
        "_id": 2,
        "id": "bff",
        "name": "Something",
        "users": [
            "xxx"
        ]
    }
]

需求说明 #

• 将permissions集合中每个权限项的users数组里前缀为group:的条目，替换为对应组下的用户（格式转为user:xxx）
• 若某权限项中已单独列出某用户（即存在user:xxx形式的条目），则该用户即使属于关联组，也不再从组中继承权限（单独授权覆盖组继承）

预期结果示例（以permissions._id = 1为例） #

[
    {
        "_id": 1,
        "resource": "resource:docs/61",
        "permissions": [
            {
                "permission": "role:documentOwner",
                "users": [
                    "user:def",
                    "user:cpo", // 从group:abc继承
                    "user:yyy"  // 从group:abc继承
                ]
            },
            {
                "permission": "document.read",
                "users": [
                    "user:abc", // 已单独列出，不重复继承
                    "user:xxx", // 已单独列出，不重复继承
                    "user:cpo", // 从group:abc继承
                    "user:yyy"  // 从group:abc继承
                ]
            },
            {
                "permission": "document.update",
                "users": [
                    "user:xxx" // 已单独列出，不重复继承
                ]
            }
        ]
    },
    // ... 其他文档结果
]

解决方案（MongoDB聚合管道） #

可以通过以下聚合查询实现需求：

db.permissions.aggregate([
    // 展开permissions数组，逐个处理每个权限项
    { $unwind: "$permissions" },
    // 拆分users数组为单独条目
    { $unwind: "$permissions.users" },
    // 区分用户/组条目，提取对应ID
    {
        $addFields: {
            "isGroup": { $eq: [{ $substrCP: ["$permissions.users", 0, 6] }, "group:"] },
            "groupId": {
                $cond: {
                    if: { $eq: [{ $substrCP: ["$permissions.users", 0, 6] }, "group:"] },
                    then: { $substrCP: ["$permissions.users", 6, { $strLenCP: "$permissions.users" }] },
                    else: null
                }
            },
            "directUser": {
                $cond: {
                    if: { $eq: [{ $substrCP: ["$permissions.users", 0, 5] }, "user:"] },
                    then: { $substrCP: ["$permissions.users", 5, { $strLenCP: "$permissions.users" }] },
                    else: null
                }
            }
        }
    },
    // 关联groups集合获取组用户列表
    {
        $lookup: {
            from: "groups",
            localField: "groupId",
            foreignField: "id",
            as: "groupData"
        }
    },
    // 展开组数据（保留空值避免丢失直接用户条目）
    { $unwind: { path: "$groupData", preserveNullAndEmptyArrays: true } },
    // 转换组用户格式，同时保留直接用户信息
    {
        $project: {
            "_id": 1,
            "resource": 1,
            "permissions.permission": 1,
            "directUser": 1,
            "groupUsers": {
                $cond: {
                    if: "$isGroup",
                    then: { $map: { input: "$groupData.users", as: "u", in: { $concat: ["user:", "$$u"] } } },
                    else: []
                }
            }
        }
    },
    // 按文档和权限分组，收集直接用户与组用户
    {
        $group: {
            _id: {
                docId: "$_id",
                resource: "$resource",
                permission: "$permissions.permission"
            },
            directUsers: { $addToSet: "$directUser" },
            allGroupUsers: { $addToSet: "$groupUsers" }
        }
    },
    // 合并组用户数组，格式化直接用户为user:xxx格式
    {
        $addFields: {
            "mergedGroupUsers": {
                $reduce: {
                    input: "$allGroupUsers",
                    initialValue: [],
                    in: { $setUnion: ["$$value", "$$this"] }
                }
            },
            "formattedDirectUsers": {
                $map: {
                    input: { $filter: { input: "$directUsers", cond: { $ne: ["$$this", null] } } },
                    as: "u",
                    in: { $concat: ["user:", "$$u"] }
                }
            }
        }
    },
    // 过滤组用户，移除已在直接用户中的条目
    {
        $addFields: {
            "filteredGroupUsers": {
                $filter: {
                    input: "$mergedGroupUsers",
                    cond: { $not: { $in: ["$$this", "$formattedDirectUsers"] } }
                }
            }
        }
    },
    // 合并直接用户与过滤后的组用户，生成最终users数组
    {
        $project: {
            "_id": "$_id.docId",
            "resource": "$_id.resource",
            "permissions": {
                permission: "$_id.permission",
                users: { $concatArrays: ["$formattedDirectUsers", "$filteredGroupUsers"] }
            }
        }
    },
    // 将权限项重组回数组
    {
        $group: {
            _id: "$_id",
            resource: { $first: "$resource" },
            permissions: { $push: "$permissions" }
        }
    },
    // 按文档ID排序
    { $sort: { "_id": 1 } }
])

核心逻辑说明 #

1. 拆分与识别：通过$unwind拆分嵌套数组，用字符串截取区分用户和组条目
2. 关联组数据：用$lookup拉取对应组的用户列表，转换为统一的user:xxx格式
3. 去重过滤：收集所有直接授权的用户，从组继承用户中排除已存在的直接用户
4. 结构重组：将处理后的权限项重新组合回原文档结构，保持数据完整性

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