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:

1. 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)
2. 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?