Replace items in array with property from matching object in another array

Question

There's a permissions collection that contains permissions and the users or groups that are assigned that permission for a given resource.

permissions

[{
  "_id": 1,
  "resource": "resource:docs/61",
  "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"]
    }
  ]
}]

And a groups collection that assigns users to a group.

groups

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

I'm trying to query the permissions collection for resource:docs/61 and for each permission, resolve any groups in the users property to the matching group's users. See below for desired result.

Desired Result

{
  "resource": "resource:docs/61",
  "permissions": [
    {
      "permission": "role:documentOwner",
      "users": [
        "user:abc",
        "user:def",
        "user:cpo",
        "user:yyy",
        "user:xxx"
      ]
    },
    {
      "permission": "document.read",
      "users": ["user:xxx"]
    },
    {
      "permission": "document.update",
      "users": ["user:xxx"]
    }
  ]
}

I've setup a Mongo Playground where I've been trying to get this to work... unsuccessfully. Below is my current attempt. I'm unsure how to map the groups to their respectful users and then reverse the $unwind. Or maybe I don't even need the $unwind 🤷‍♂️

db.permissions.aggregate([
  {
    "$match": {
      "resource": "resource:docs/61"
    }
  },
  {
    $unwind: "$permissions"
  },
  {
    "$lookup": {
      "from": "groups",
      "let": {
        "users": {
          "$filter": {
            "input": "$permissions.users",
            "as": "user",
            "cond": {
              "$ne": [
                -1,
                {
                  "$indexOfCP": [
                    "$$user",
                    "group:"
                  ]
                }
              ]
            }
          }
        }
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                {
                  "$concat": [
                    "group:",
                    "$id"
                  ]
                },
                "$$users"
              ]
            }
          }
        },
        {
          "$project": {
            "_id": 0,
            "id": {
              "$concat": [
                "group:",
                "$id"
              ]
            },
            "users": 1
          }
        }
      ],
      "as": "groups"
    }
  },
  {
    "$project": {
      "groups": 1,
      "permissions": {
        "permission": "$permissions.permission",
        "users": "permissions.users"
      }
    }
  }
])

ray · Accepted Answer · 2022-12-14 22:09:51Z

You will need to:

$unwind the permissions for easier processing in later stages
$lookup with "processed" key:

remove the prefix group: for the group key

use the $lookup result to perform $setUnion with your permissions.users array. Remember to $filter out the group entries first.
$group to get back the original / expected structure.

db.permissions.aggregate([
  {
    "$match": {
      "resource": "resource:docs/61"
    }
  },
  {
    "$unwind": "$permissions"
  },
  {
    "$lookup": {
      "from": "groups",
      "let": {
        "groups": {
          "$map": {
            "input": "$permissions.users",
            "as": "u",
            "in": {
              "$replaceAll": {
                "input": "$$u",
                "find": "group:",
                "replacement": ""
              }
            }
          }
        }
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              "$in": [
                "$id",
                "$$groups"
              ]
            }
          }
        }
      ],
      "as": "groupsLookup"
    }
  },
  {
    "$addFields": {
      "groupsLookup": {
        "$reduce": {
          "input": "$groupsLookup",
          "initialValue": [],
          "in": {
            $setUnion: [
              "$$value",
              {
                "$map": {
                  "input": "$$this.users",
                  "as": "u",
                  "in": {
                    "$concat": [
                      "user:",
                      "$$u"
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$project": {
      resource: 1,
      permissions: {
        permission: 1,
        users: {
          "$setUnion": [
            {
              "$filter": {
                "input": "$permissions.users",
                "as": "u",
                "cond": {
                  $eq: [
                    -1,
                    {
                      "$indexOfCP": [
                        "$$u",
                        "group:"
                      ]
                    }
                  ]
                }
              }
            },
            "$groupsLookup"
          ]
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      resource: {
        $first: "$resource"
      },
      permissions: {
        $push: "$permissions"
      }
    }
  }
])

Mongo Playground

You answered my last mongo question. Thanks for the quick, concise answer. Much appreciated!

Collectives™ on Stack Overflow

Replace items in array with property from matching object in another array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related