Many To Many with embedded pivot list

Question

I am trying to select all users which references a group that references a permission that has "valid" set to true (possibly multiple). To achieve this I am planning to use an aggregation with a lookup.

db={
  "users": [
    {
      "_id": "1",
      "groups": [
        "2"
      ]
    },
    {
      "_id": "2",
      "groups": [
        "1"
      ]
    }
  ],
  "group": [
    {
      "_id": "1",
      "permissions": [
        "12",
        "3"
      ]
    },
    {
      "_id": "2",
      "permissions": [
        "3",
        "2"
      ]
    }
  ],
  "permission": [
    {
      "_id": "12",
      "valid": true
    },
    {
      "_id": "3",
      "valid": true
    },
    {
      "_id": "2",
      "valid": true
    }
  ]
}

I can't think of an efficient/simple way to do this.

I have 2 ideas:

have a lookup from user to group with a pipeline that does a lookup from group to permission. The problem there is I would need to do a $match to see if the user's group list contains the groups id (which I don't know if its possible)
do a simple lookup from user to group than unwind the joined field and do a second lookup using the joined field from group to permission. Than at the end do a $match to see if any contain "valid" true and group the results back to get the original users. This idea doesnt sound very efficient though. (https://mongoplayground.net/p/JB9Yb3a65Cn)

Any ideas or inputs on this?

user_8987128 · Answer

you can use direct array of object without $unwind in lookup as localFields,
second lookup will replace group field with permissions response because it is not needed in next stage

db.users.aggregate([
  {
    $lookup: {
      from: "group",
      localField: "groups",
      foreignField: "_id",
      as: "group"
    }
  },
  {
    $lookup: {
      from: "permission",
      localField: "group.permissions",
      foreignField: "_id",
      as: "group"
    }
  },
  { $match: { "group.valid": true } },
  { $unset: "group" }
])

Playground

Many To Many with embedded pivot list

1 Answers