Querying with mongoose/mongoDB on nested document

Question

I have a car model given as below

{
    "_id": "54b8a71843286774060b8bed",
    "name": "Car1",
    "active": true,
    "model": [
        {
            "name": "Model1",
            "active": true,
            "_id": "54b8a71843286774060b8bee",
            "available": [
                {
                    "Day": "Mon",
                    "quantity": "6"
                },
                {
                    "Day": "Tue",
                    "quantity": "6"
                },
                {
                    "Day": "Wed",
                    "quantity": "6"
                },
                {
                    "Day": "Thurs",
                    "quantity": "6"
                },
                {
                    "Day": "Fri",
                    "quantity": "0"
                }
            ]
        },
        {
            "name": "Model2",
            "active": true,
            "_id": "54b8a71843286774060b8bef",
            "available": [
                {
                    "Day": "Mon",
                    "quantity": "6"
                },
                {
                    "Day": "Tue",
                    "quantity": "6"
                },
                {
                    "Day": "Wed",
                    "quantity": "6"
                },
                {
                    "Day": "Thurs",
                    "quantity": "6"
                },
                {
                    "Day": "Fri",
                    "quantity": "6"
                }
            ]
        },
        {
            "name": "Model3",
            "active": true,
            "_id": "54b8a71843286774060b8beg",
            "available": [
                {
                    "Day": "Mon",
                    "quantity": "6"
                },
                {
                    "Day": "Tue",
                    "quantity": "6"
                },
                {
                    "Day": "Wed",
                    "quantity": "6"
                },
                {
                    "Day": "Thurs",
                    "quantity": "6"
                },
                {
                    "Day": "Fri",
                    "quantity": "0"
                }
            ]
        }
    ]
}

I am trying to search availability of car on given days. Like if I select Friday then it should return me cars whose quantity more than 0 on Friday but currently it is returning all the cars having quantity 0 as well.

I have written query as below

Car.find({ 'active': true, 'model.available': { $elemMatch: { quantity: {$gte : 1} } } })

But it returning documents those are having quantity 0 also.

Answer 1

For this, you'll need the aggregation pipeline.

The following code snippet does this:

1. Find all documents with at least one matching model.
2. Split up the documents: a document with an array of 3 models in it gets turned into three documents with one model each:
   {name: "Car1": 1, models: [{name: "Model1"}, {name: "Model2"}, {name: "Model3"}]}
   Becomes:
   {name: "Car1", models: {name: "Model1"}} & {name: "Car1", models: {name: "Model2"}} & {name: "Car1", models: {name: "Model3"}}.
3. The split up documents are filtered (again) on quantity and day.
4. Optionally, glue the documents back together again. You might not need that in your application.

---

db.cars.aggregate([
    // First, we do your query, which will return 
    // every document that has _at least one_
    // model that is available.
    {$match: {
        'active': true,
        'model.available': {$elemMatch: { 
            'quantity': {$gte: 1},
            'Day': 'Fri'
        }}
    }},
    // We split up the found documents,
    // every document will now have exactly
    // one 'model' in it.
    {$unwind: "$model"},
    // We now filter the split documents.
    {$match: {
        'model.available': {$elemMatch: { 
            'quantity': {$gte: 1},
            'Day': 'Fri'
        }}
    }},
    // If you want, you can now glue the
    // models back together again.
    {$group: {
        _id:    "$_id", // Group all documents with the same _id
        name:   {$first: "$name"},
        active: {$first: "$active"},
        model:  {$push: "$model"}  // Make an array of models
    }}
])

Important note: For $gte to work, you'll need to store your quantity as a Number, not a String. Since your example has the numbers stored as strings, you might want to double check them in your database.