How to get values with filter depends on other collection in MongoDB-CodePudding

I have 2 collectionsEmployee and Role. Now I want to get all name from Employee without title software engineer in collection Role. The valid answer for example below is [Lisa, Nick]. How to solve this problem. Thank you so much.

Employee

[
  {
    "id": "001",
    "name": "John"
  },
  {
    "id": "002",
    "name": "Lisa"
  },
  {
    "id": "003",
    "name": "Zery"
  },
  {
    "id": "004",
    "name": "Nick"
  }
]

Role

[
  {
    "employee_id": "001",
    "title": "software engineer"
  },
  {
    "employee_id": "001",
    "title": "data scientist"
  },
  {
    "employee_id": "002",
    "title": "data engineer"
  },
  {
    "employee_id": "002",
    "title": "data scientist"
  },
  {
    "employee_id": "002",
    "title": "data analyst"
  },
  {
    "employee_id": "003",
    "title": "software engineer"
  }
]

CodePudding user response：

Here's one way you could do it.

db.Employees.aggregate([
  {
    "$lookup": {
      "from": "Roles",
      "localField": "id",
      "foreignField": "employee_id",
      "as": "roles"
    }
  },
  {
    "$match": {
      "roles.title": {
        "$nin": ["software engineer"]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "name": 1
    }
  }
])

Try it on mongoplayground.net.