Pymongo find value in subdocuments

问题:

I'm using MongoDB 4 and Python 3. I have 3 collections. The first collection got 2 referenced fields on the other collections.

Example :

User {
   _id  : ObjectId("5b866e8e06a77b30ce272ba6"),
   name : "John",
   pet  : ObjectId("5b9248cc06a77b09a496bad0"),
   car  : ObjectId("5b214c044ds32f6bad7d2"),
}

Pet {
   _id  : ObjectId("5b9248cc06a77b09a496bad0"),
   name : "Mickey",
}

Car {
   _id   : ObjectId("5b214c044ds32f6bad7d2"),
   model : "Tesla"
}

So one User has one car and one pet. I need to query the User collection and find if there is a User who has a Pet with the name "Mickey" and a Car with the model "Tesla".

I tried this :

db.user.aggregate([{
    $project : {"pet.name" : "Mickey", "car.model" : "Tesla"  } 
}])

But it returns me lot of data while I have just one document with this data. What I'm doing wrong ?

回答1:

The answer posted by @AnthonyWinzlet has the downside that it needs to churn through all documents in the users collection and perform $lookups which is relatively costly. So depending on the size of your Users collection it may well be faster to do this:

1. Put an index on users.pet and users.car: db.users.createIndex({pet: 1, car: 1})
2. Put an index on cars.model: db.cars.createIndex({model: 1})
3. Put an index on pets.name: db.pets.createIndex({name: 1})

Then you could simply do this:

1. Get the list of all matching "Tesla" cars: db.cars.find({model: "Tesla"})
2. Get the list of all matching "Mickey" pets: db.pets.find({name: "Mickey"})
3. Find the users you are interested in: db.users.find({car: { $in: [<ids from cars query>] }, pet: { $in: [<ids from pets query>] }})

That is pretty easy to read and understand plus all three queries are fully covered by indexes so they can be expected to be as fast as things can get.

回答2:

You need to use $lookup aggregation here.

Something like this

db.users.aggregate([
  { "$lookup": {
    "from": Pet.collection.name,
    "let": { "pet": "$pet" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$pet"] }, "name" : "Mickey"}}
    ],
    "as": "pet"
  }},
  { "$lookup": {
    "from": Car.collection.name,
    "let": { "car": "$car" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$car"] }, "model" : "Tesla"}}
    ],
    "as": "car"
  }},
  { "$match": { "pet": { "$ne": [] }, "car": { "$ne": [] } }},
  { "$project": { "name": 1 }}
])