query MongoDB when two fields match two values at the same time

Question

I have a collection that keeps applications' allowed actions like this:

const appAccess = [
  {
    appId: 111,
    scope: 'get-user',
  },
  {
    appId: 111,
    scope: 'update-user',
  },
  {
    appId: 111,
    scope: 'delete-user',
  },
  {
    appId: 222,
    scope: 'get-user',
  },
  {
    appId: 222,
    scope: 'update-user',
  },
  {
    appId: 333,
    scope: 'delete-user',
  },
];

also I have a collection that keeps logs of application actions on users, this is an example in that collection:

const appActions = [
  {
    _id: 123,
    appId: 111,
    scope: 'get-user',
    user: 10,
  },
  {
    _id: 456,
    appId: 111,
    scope: 'update-user',
    user: 20,
  },
  {
    _id: 567,
    appId: 111,
    scope: 'delete-user',
    user: 30,
  },
  {
    _id: 678,
    appId: 222,
    scope: 'update-user',
    user: 50,
  },
  {
    _id: 789,
    appId: 222,
    scope: 'get-user',
    user: 60,
  },
  {
    _id: 890,
    appId: 333,
    scope: 'delete-user',
    user: 70,
  },
];

now imagine I want to select all update-user actions done by application 111 and all get-user actions done by application 222, how can I do that?

Answer 1

You can use the conditional $lookup syntax, like so:

db.actions.aggregate([
  {
    $match: {
      scope: "delete-user"//get actions
      
    }
  },
  {
    "$lookup": {
      "from": "performed_actions",
      "let": {
        appId: "$appId",
        scope: "$scope"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$appId",
                    "$appId"
                  ]
                },
                {
                  $eq: [
                    "$$scope",
                    "$scope"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "matchedActions"
    }
  }
])

Mongo Playground