match condition on on foreign collection with lookup in mongoDB-CodePudding

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?

CodePudding user response：

It can be done with two lookup

First get society name times and then look up on those two collection

Playground: https://mongoplayground.net/p/q-oCBjm-0hQ

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

CodePudding user response：

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.