CODEWITHSUNDEEP
pythonmongodbpymongo
John
John

Reputation: 4991

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 ?

Upvotes: 1

Views: 309

Answers (2)

Ashh
Ashh

Reputation: 46491

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 }}
])

Upvotes: 1

dnickless
dnickless

Reputation: 10918

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.

Upvotes: 1

Related Questions