How to find document and single subdocument matching given criterias in MongoDB collection

Question

I have collection of products. Each product contains array of items.

> db.products.find().pretty()
{
    "_id" : ObjectId("54023e8bcef998273f36041d"),
    "shop" : "shop1",
    "name" : "product1",
    "items" : [
            {
                    "date" : "01.02.2100",
                    "purchasePrice" : 1,
                    "sellingPrice" : 10,
                    "count" : 15
            },
            {
                    "date" : "31.08.2014",
                    "purchasePrice" : 10,
                    "sellingPrice" : 1,
                    "count" : 5
            }
    ]
}

So, can you please give me an advice, how I can query MongoDB to retrieve all products with only single item which date is equals to the date I pass to query as parameter.

The result for "31.08.2014" must be:

    {
    "_id" : ObjectId("54023e8bcef998273f36041d"),
    "shop" : "shop1",
    "name" : "product1",
    "items" : [
            {
                    "date" : "31.08.2014",
                    "purchasePrice" : 10,
                    "sellingPrice" : 1,
                    "count" : 5
            }
    ]
}

Answer 1

What you are looking for is the positional $ operator and "projection". For a single field you need to match the required array element using "dot notation", for more than one field use $elemMatch:

db.products.find(
    { "items.date": "31.08.2014" },
    { "shop": 1, "name":1, "items.$": 1 }
)

Or the $elemMatch for more than one matching field:

db.products.find(
    { "items":  { 
        "$elemMatch": { "date": "31.08.2014",  "purchasePrice": 1 }
    }},
    { "shop": 1, "name":1, "items.$": 1 }
)

These work for a single array element only though and only one will be returned. If you want more than one array element to be returned from your conditions then you need more advanced handling with the aggregation framework.

db.products.aggregate([
    { "$match": { "items.date": "31.08.2014" } },
    { "$unwind": "$items" },
    { "$match": { "items.date": "31.08.2014" } },
    { "$group": {
        "_id": "$_id",
        "shop": { "$first": "$shop" },
        "name": { "$first": "$name" },
        "items": { "$push": "$items" }
    }}
])

Or possibly in shorter/faster form since MongoDB 2.6 where your array of items contains unique entries:

db.products.aggregate([
    { "$match": { "items.date": "31.08.2014" } },
    { "$project": {
        "shop": 1,
        "name": 1,
        "items": {
            "$setDifference": [
                { "$map": {
                    "input": "$items",
                    "as": "el",
                    "in": {
                        "$cond": [
                            { "$eq": [ "$$el.date", "31.08.2014" ] },
                            "$$el",
                            false 
                        ]
                    }
                }},
                [false]
            ]
        }
    }}
])

Or possibly with $redact, but a little contrived:

db.products.aggregate([
    { "$match": { "items.date": "31.08.2014" } },
    { "$redact": {
        "$cond": [
             { "$eq": [ { "$ifNull": [ "$date", "31.08.2014" ] }, "31.08.2014" ] },
             "$$DESCEND",
             "$$PRUNE"
         ]
    }}
])

More modern, you would use $filter:

db.products.aggregate([
  { "$match": { "items.date": "31.08.2014" } },
  { "$addFields": {
    "items": {
      "input": "$items",
      "cond": { "$eq": [ "$$this.date", "31.08.2014" ] }
    }
  }}
])

And with multiple conditions, the $elemMatch and $and within the $filter:

db.products.aggregate([
  { "$match": { 
    "$elemMatch": { "date": "31.08.2014",  "purchasePrice": 1 }
  }},
  { "$addFields": {
    "items": {
      "input": "$items",
      "cond": { 
        "$and": [
          { "$eq": [ "$$this.date", "31.08.2014" ] },
          { "$eq": [ "$$this.purchasePrice", 1 ] }
        ]
      }
    }
  }}
])

So it just depends on whether you always expect a single element to match or multiple elements, and then which approach is better. But where possible the .find() method will generally be faster since it lacks the overhead of the other operations, which in those last to forms does not lag that far behind at all.

As a side note, your "dates" are represented as strings which is not a very good idea going forward. Consider changing these to proper Date object types, which will greatly help you in the future.