I would like to select from this 2 collections the users with moderator roles.

USERS collection

[
  {
    _id: "701",
    username: "user1",
    roles: [
      "617",
      "618"
    ]
  },
  {
    _id: "702",
    username: "user2",
    roles: [
      "617"
    ]
  },
  {
    _id: "703",
    username: "user3",
    roles: [
      "617",
      "619"
    ]
  },
  {
    _id: "704",
    username: "user4",
    roles: [
      "617",
      "619"
    ]
  }
]

ROLES collection

[
  {
    _id: "617",
    name: "simpleuser"
  },
  {
    _id: "618",
    name: "admin"
  },
  {
    _id: "619",
    name: "moderator"
  }
]

In SQL would be something like this:

SELECT * FROM USERS
JOIN ROLES ON ROLE_ID = USER_ROLES
WHERE ROLE_NAME = "moderator"

I can not figure it out with mongodb mongoose. Please help me out. Thanks.

CodePudding user response：

You can use a $lookup with pipeline like this:

• First $lookup matching the role id using $in because it is in an array and $eq to compare the role.
• This generates a field called roles which overwrite the existing field (you can change the name if you don't want to overwrite) and then uses $match to match the documents where the roles is not empty (i.e. there is a coindicence with roles table).

db.users.aggregate([
  {
    "$lookup": {
      "from": "roles",
      "let": {"roles": "$roles"},
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                {"$in": ["$_id","$$roles"]},
                {"$eq": ["$name","moderator"]}
              ]
            }
          }
        }
      ],
      "as": "roles"
    }
  },
  {
    "$match": {
      "roles": {"$ne": [] }
    }
  }
])

Example here

Using mongoose you can do:

const result = await yourCollection.aggregate(/*the query*/)
res.status(200).send(result) // or whatever

Or using callback

yourCollection.aggregate(/*the query*/).then(result => {res.status(200).send(result)})

Also, you can use $project stage to send fields you want to the frontend, for example, to send only username you can use this query