Mongodb: How to filter out array objects from $lookup result by matching lists of ObjectIds-CodePudding

I'm building a view that compiles data from a few different collections so that I don't have to make multiple queries during an API call. I'd like to do some filtering on top of the basic "localField" "foreignField" filtering but can't quite seem to get the right result no matter what I do. The problem seems to be that I need to check which objects of the array contain an array that has an ObjectId included in another array.

I'm using aggregate function while trying to tweak my results. It looks like this:

db.users.aggregate([{ $lookup: { from: "organizations", localField: "context", foreignField: "_id", as: "context" } }, { $lookup: { from: "roles", localField: "roles", foreignField: "_id", as: "roles_with_details" } }, { $lookup: { from: "service_modules", localField: "context.enabled_service_modules", foreignField: "_id", as: "service_modules" } }, { $project: { "context.enabled_service_modules": 0, "password": 0 } }])

This returns data in the following format:

[
  {
    _id: ObjectId("63907d27ba21557a3455a24b"),
    first_name: 'Tep',
    last_name: 'Tes',
    display_name: 'Tep Tes',
    created: ISODate("2022-12-07T11:46:47.230Z"),
    last_seen: 1670618355349,
    username: '[email protected]',
    email: '[email protected]',
    enable_local_login: 'true',
    connected_logins: [],
    roles: [
      ObjectId("63907c7fba21557a3455a247"),
      ObjectId("63907c7fba21557a3455a248")
    ],
    favourite_sm: [ ObjectId("6390832cba21557a3455a250") ],
    context: [
      {
        _id: ObjectId("639074e7ba21557a3455a23f"),
        organization_name: 'vip',
        display_name: 'Vip',
        enabled_login_methods: [ 'ldap' ],
        login_method_configurations: [],
        created: ISODate("2022-12-07T11:11:35.568Z")
      }
    ],
    roles_with_details: [
      {
        _id: ObjectId("63907c7fba21557a3455a247"),
        role: 'jt',
        description: 'Has access to membership data'
      },
      {
        _id: ObjectId("63907c7fba21557a3455a248"),
        role: 'at',
        description: 'Has access to tenant information'
      }
    ],
    service_modules: [
      {
        _id: ObjectId("6390832cba21557a3455a250"),
        service_name: 'Jt',
        permissions: [
          ObjectId("63907c7fba21557a3455a245"),
          ObjectId("63907c7fba21557a3455a246"),
          ObjectId("63907c7fba21557a3455a247")
        ],
        route: 'jt'
      },
      {
        _id: ObjectId("6390832cba21557a3455a24c"),
        service_name: 'Mc',
        permissions: [
          ObjectId("63907c7fba21557a3455a245"),
          ObjectId("63907c7fba21557a3455a246")
        ],
        route: 'mc'
      },
      {
        _id: ObjectId("6390832cba21557a3455a24e"),
        service_name: 'Ms',
        permissions: [
          ObjectId("63907c7fba21557a3455a245"),
          ObjectId("63907c7fba21557a3455a246")
        ],
        route: 'ms'
      },
      {
        _id: ObjectId("6390832cba21557a3455a251"),
        service_name: 'At',
        permissions: [
          ObjectId("63907c7fba21557a3455a245"),
          ObjectId("63907c7fba21557a3455a246"),
          ObjectId("63907c7fba21557a3455a248")
        ],
        route: 'at'
      }
    ]
  }
]

I would like to filter this result so that "service_modules" array would only include objects which contain at least one ObjectId in their "service_modules.permissions" array, that is also found in the "roles" array. This would mean that the "service_modules" should look like this:

    service_modules: [
      {
        _id: ObjectId("6390832cba21557a3455a250"),
        service_name: 'Jt',
        permissions: [
          ObjectId("63907c7fba21557a3455a245"),
          ObjectId("63907c7fba21557a3455a246"),
          ObjectId("63907c7fba21557a3455a247")
        ],
        route: 'jt'
      },
      {
        _id: ObjectId("6390832cba21557a3455a251"),
        service_name: 'At',
        permissions: [
          ObjectId("63907c7fba21557a3455a245"),
          ObjectId("63907c7fba21557a3455a246"),
          ObjectId("63907c7fba21557a3455a248")
        ],
        route: 'at'
      }
    ]

I have tried modifying the $lookup, which joins the service_modules collection, to following trying to use pipeline to match the arrays:

{ $lookup: { from: "service_modules", localField: "context.enabled_service_modules", foreignField: "_id","let":{"sm":"$service_modules","roles":"$roles"},"pipeline":[{$match:{$expr:{$in:["$$sm.permissions","$$roles"]}}}], as: "service_modules" } }

But it gives a following error:

MongoServerError: PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: missing

I also tried using unwind and group, but trying to unwind with:

{$unwind:"$service_modules.permissions"}

results in empty set, so I wasn't able to proceed to the grouping stage.

How should I filter the $lookup in order to get the result I'd want?

CodePudding user response：

One way you could do it is by using a "$match" in a "pipeline" of the service_modules "$lookup".

db.users.aggregate([
  {
    $lookup: {
      from: "organizations",
      localField: "context",
      foreignField: "_id",
      as: "context"
    }
  },
  {
    $lookup: {
      from: "roles",
      localField: "roles",
      foreignField: "_id",
      as: "roles_with_details"
    }
  },
  {
    $lookup: {
      from: "service_modules",
      localField: "context.enabled_service_modules",
      foreignField: "_id",
      as: "service_modules",
      "let": {
        "roles": "$roles"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$gt": [
                {"$size": {"$setIntersection": ["$$roles", "$permissions"]}},
                0
              ]
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      "context.enabled_service_modules": 0,
      "password": 0
    }
  }
])

Try it on mongoplayground.net.