match condition on on foreign collection with lookup in mongoDB

Question

I have the 3 collections

users: [
{_id: "aaa", name: "John", department: 1},
{_id: "bbb", name: "Charles", department: 1},
{_id: "ccc", name: "Jessy", department: 1"},
{_id: "ddd", name: "Tim", department: 2},
{_id: "eee", name: "Max", department: 2},
{_id: "fff", name: "Julia", department: 2},
{_id: "ggg", name: "Arnold", department: 3}
]

departments: [
{_id: 1, name: "press", society: "times"},
{_id: 2, name: "news", society: "times"},
{_id: 3, name: "infos", society: "herald"}
]

society: [
{name: "times", country: "England"},
{name: "herald", country: "USA"}
]

A user work in a department and a department is in a society.

I wanto to do 2 requests, the first one is to have all users from the society "times" and the second, is to have all users from the country "England".

I tried this request for the first one :

db.users.aggregate([
{'$match': {'dept.society': "times"}
{
            '$lookup': {
                'from': "departments",
                'localField': "department",
                'foreignField': "_id",
                'as': "dept"
            }
        }])

But because of the condition is on foreign collection ("departments"), it seems not working. Only condition on local collection works ('department': 1). How I can do that, and it's the same problem for country request?

Answer 1

There are many ways to achieve your two queries. I also see that you want to begin the aggregations with the users collection.

Here's one way to query for:

all users from the society "times"

db.users.aggregate([
  {
    "$lookup": {
      "from": "departments",
      "localField": "department",
      "foreignField": "_id",
      "pipeline": [
        {
          "$match": {
            "society": "times"  // input society
          }
        }
      ],
      "as": "deptLookup"
    }
  },
  { "$match": { "$expr": { "$gt": [ { "$size": "$deptLookup" }, 0 ] } } },
  { "$unset": "deptLookup" }
])

Try it on mongoplayground.net.

Here's one way to query for:

all users from the country "England"

db.users.aggregate([
  {
    "$lookup": {
      "from": "departments",
      "localField": "department",
      "foreignField": "_id",
      "pipeline": [
        {
          "$lookup": {
            "from": "society",
            "localField": "society",
            "foreignField": "name",
            "pipeline": [
              {
                "$match": {
                  "country": "England"  // input country
                }
              }
            ],
            "as": "socLookup"
          }
        },
        { "$match": { "$expr": { "$gt": [ { "$size": "$socLookup" }, 0 ] } } }
      ],
      "as": "deptSocLookup"
    }
  },
  { "$match": { "$expr": { "$gt": [ { "$size": "$deptSocLookup" }, 0 ] } } },
  { "$unset": "deptSocLookup" }
])

Try it on mongoplayground.net.