Mongo如何加入两个集合并在第二个集合上添加条件

Question

I have two collections users{id, name} and files{id, userId, name} I want to find all the files whose file name "abc.xyz", I tried to write a code using $lookup but getting all the files belong to user and not filtering it by name "abc.xyz", I have written following query.

db.user.aggregate([
{"$lookup": 
    {
        "from": "files", 
        "localField": "id", 
        "foreignField": "userId", 
        "as": "fileList"
    }
},
{"$project": { "filList":{
    "$filter": {
                    "input":"$fileList",
                    "as":"file"
                    "cond": {"$eq": ["$file.name","abc.xyz"]}
           }
             }
         }
}
])

Thank you

Answer 1

I want to find all the files whose file name "abc.xyz" … but getting all the files belong to user and not filtering it by name "abc.xyz"

Based on your question above, it could also be interpreted as "Find all files with name abc.xyz along with its owner information".

In which case, it would be better to filter the files collection first using $match to filter file name equal to abc.xyz. This would limit the number of documents to look-up into users collection, instead of perfoming lookup for both collections then perform filtering.

For example:

db.files.aggregate([
    {"$match": {"name":"abc.xyz"}},
    {"$lookup": {
                "from": "users", 
                "localField": "userId", 
                "foreignField": "_id", 
                "as": "users"
                 }
     }]);

Please note that the collection is now reversed, from files looking up into users. An example result would be:

  "result": [
    {
      "_id": 111,
      "userId": 999,
      "name": "abc.xyz",
      "owner": [
        {
          "_id": 999,
          "name": "xmejx"
        }
      ]
    },
    {
      "_id": 222,
      "userId": 998,
      "name": "abc.xyz",
      "owner": [
        {
          "_id": 998,
          "name": "kalwb"
        }
      ]
    }
  ]

I would also recommend to check out:

• Data Model Examples with Patterns
• Aggregation Pipeline Optimisation