Mongodb - multiple collections

Question

I have 3 collections in Mongo (v5.0) and I want to get a subset of a collection using values in other collections. I worked with options like $redact, $lookup but did not get the right results. The most important part is array lookup in an array.

Users

[
{ "id": "a@example.com", "region": "US"},
{ "id": "b@example.com", "region": "EU"},
{ "id": "c@example.com", "region": "EU"},
{ "id": "d@example.com", "region": "US"},
]

Group

{ 
"HR": [ "a@example.com", "c@example.com" ],
"IT": [ "b@example.com", "d@example.com" ]
}

and I want to filter all users from a third collection called

Rules

[ 
"group" : ["HR"],
"region" : ["US", "EU"]
]

so final outcome should be "All users in HR in US or EU region"

[
{ "id": "a@example.com", "region": "US"},
{ "id": "c@example.com", "region": "EU"},
]

Please help.

Answer 1

Unsure how the performance of this query is executed, but it results in the expected outcome:

1. $lookup - Join users and groups collections:

   1.1. $filter - Filter the document from the result 1.1.1 with k is not _id (search for "HR" and "IT" only) and v array contains user_id via $in.

   1.1.1. $objectToArray - Convert key-value pair into documents.

   1.2. $unwind - Deconstruct the groups array into documents.

   1.3. $replaceWith - Replace the input documents with groups field.

2. $lookup - Join with rules collection:

   2.1. $filter - Filter the documents with:

   2.1.1. $size & $setIntersection - There is at least 1 element with groups_dept (a.k.a. groups.k) variable intersect with groups.

   2.1.2. $in - region variable is in ($in) region array.

3. $match - Filter the document with rules is not an empty array. (Mean fulfill the rules criteria.

4. $unset - Remove rules and groups fields.

db.users.aggregate([
  {
    $lookup: {
      from: "groups",
      let: {
        user_id: "$id"
      },
      pipeline: [
        {
          $project: {
            groups: {
              $filter: {
                input: {
                  $objectToArray: "$$ROOT"
                },
                cond: {
                  $and: [
                    {
                      $not: {
                        $eq: [
                          "_id",
                          "$$this.k"
                        ]
                      }
                    },
                    {
                      $in: [
                        "$$user_id",
                        "$$this.v"
                      ]
                    }
                  ]
                }
              }
            }
          }
        },
        {
          $unwind: "$groups"
        },
        {
          $replaceWith: "$groups"
        }
      ],
      as: "groups"
    }
  },
  {
    $lookup: {
      from: "rules",
      let: {
        groups_dept: "$groups.k",
        region: "$region"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $gt: [
                    {
                      $size: {
                        $setIntersection: [
                          "$$groups_dept",
                          "$group"
                        ]
                      }
                    },
                    0
                  ]
                },
                {
                  $in: [
                    "$$region",
                    "$region"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "rules"
    }
  },
  {
    $match: {
      rules: {
        $not: {
          $eq: []
        }
      }
    }
  },
  {
    $unset: [
      "rules",
      "groups"
    ]
  }
])

Sample Mongo Playground