CODEWITHSUNDEEP
arraysdatabasemongodbmongoosemongodb-query
G. Modebadze
G. Modebadze

Reputation: 975

How to specify fields in MongoDB with multiple condition find query?

This is Single document: 

{
   _id: "...",
   firstName: "john",
   lastName:"Doe",
   cars: [
       {
           "_id": "...",
           "carName": "BMW",
           "carModel": "330",
           "carColor": "silver"
       },
       {
           "_id": "...",
           "carName": "Lexus",
           "carModel": "IS300",
           "carColor": "white"
       },
       {
           "_id": "...",
           "carName": "LADA",
           "carModel": "2106",
           "carColor": "blue"
       }
   ]
}

I am trying to select John's BMW's "carColor" only. Something like this:

db.persons.findOne(
        { "firstName": "John", "cars.carName": "BMW" },
        { "_id": 0, "cars.$.carColor": 1 }
      );

But this query returns full object like this: 

{
    cars: [
      {
         "_id": "...",
         "carName": "BMW",
         "carModel": "330",
         "carColor": "silver"
      }
}

I tried different query already without .$. symbol:

db.persons.findOne(
            { "firstName": "John", "cars.carName": "BMW" },
            { "_id": 0, "cars.carColor": 1 }
          );

This version returns only "carColor" properties, but without filtering "carName". Like this:

{
   cars: [
       {
          "carColor": "silver"
       },
       {
          "carColor": "white"
       },
       {
          "carColor": "blue"
       }
   ]
}

Any ideas?

Upvotes: 2

Views: 5263

Answers (3)

dnickless
dnickless

Reputation: 10918

If you know for a fact that you have not more than one "BMW" value in your array then here is a way of getting the result using a single $project stage:

db.getCollection('collection').aggregate([{
    $match: {
        "firstName": "john"
        /* for performance reasons, you may want to include the following line which, however, is not required */
        /* this makes sense if you have lots of "john"s with different sets of cars in your database */
        , "cars.carName": "BMW" // this will use an index on "cars.carName" if available
    }
}, {
    $project: {
        _id: 0, // do not return the _id field
        color: {
            $reduce: { // transform the filtered input array
                "input": {
                    $filter: { // remove all non-"BMW" cars from the "cars" array
                        input: "$cars",
                        as: "car",
                        cond: { $eq: [ "$$car.carName", "BMW" ] }
                    }
                },
                "initialValue": null,
                "in": "$$this.carColor" // just return the color value, nothing else
            }
        }
    }
}])

Upvotes: 0

Rubin Porwal
Rubin Porwal

Reputation: 3845

db.persons.find({
  firstName: 'john',
  cars: {
    $elemMatch: {
      carName: 'BMW'
    }
  }
},
{
  'cars.$': 1
})

Upvotes: 0

matthPen
matthPen

Reputation: 4343

Why it's not working?

{"firstName": "John", "cars.carName": "BMW"}

means 'where the name is john and where there's AT LEAST one entry in cars array where carName is "BMW"'. But it return complete document, without filtering array.

{ "_id": 0, "cars.carColor": 1 }

don't projects _id, but projects carColor of all entries of cars array.

SOLUTION

In fact, you can't achieve exactly what you want with find and projection methods. The better you can do is adding $ projection operator like this : 

db.collection.find({
  firstName: "john",
  "cars.carName": "BMW"
},
{
  _id: 0,
      "cars.$": 1
    })

**RESULT**

[
  {
    "cars": [
      {
        "_id": "...",
        "carColor": "silver",
        "carModel": "330",
        "carName": "BMW"
      }
    ]
  }
]

But this method have drawbacks :

  • you get entire array entry, and not only the color as you want/need
  • It return only the first matching entry : if john has 2 BMW, only one will be returned.

BETTER SOLUTION

Fortunately, MongoDB provides an other way to achieve this, with aggregation framework, and $filter operator : 

db.collection.aggregate([
  {
    $match: {
      firstName: "john"
    }
  },
  {
    $project: {
      cars: {
        $filter: {
          input: "$cars",
          as: "cars",
          cond: {
            $eq: [
              "$$cars.carName",
              "BMW"
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      "colors": "$cars.carColor"
    }
  }
])

You can try it here.

EDIT : Other solution

you can try this too, with unwind/group stages : 

db.collection.aggregate([
  {
    $match: {
      firstName: "john"
    }
  },
  {
    $unwind: "$cars"
  },
  {
    $match: {
      "cars.carName": "BMW"
    }
  },
  {
    $group: {
      "_id": null,
      colors: {
        $push: "$cars.carColor"
      }
    }
  }
])

Upvotes: 2

Related Questions