CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
bflemi3
bflemi3

Reputation: 6790

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

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"
      }
    }
  }
])

Upvotes: 1

Views: 378

Answers (1)

ray
ray

Reputation: 15227

You will need to:

  1. $unwind the permissions for easier processing in later stages
  2. $lookup with "processed" key:
  • remove the prefix group: for the group key
  1. use the $lookup result to perform $setUnion with your permissions.users array. Remember to $filter out the group entries first.
  2. $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

Upvotes: 1

Related Questions