CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
evgeniy44
evgeniy44

Reputation: 3162

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

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

Upvotes: 19

Views: 33991

Answers (3)

Xairoo
Xairoo

Reputation: 345

Based on Neil Lunn's code I work with this solution, it includes automatically all first level keys (but you could also exclude keys if you want):

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

With multiple requirements:

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

Upvotes: 0

Neil Lunn
Neil Lunn

Reputation: 151190

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.

Upvotes: 42

Jared Alessandroni
Jared Alessandroni

Reputation: 177

Mongo supports dot notation for sub-queries.

See: http://docs.mongodb.org/manual/reference/glossary/#term-dot-notation

Depending on your driver, you want something like: 

db.products.find({"items.date":"31.08.2014"});

Note that the attribute is in quotes for dot notation, even if usually your driver doesn't require this.

Upvotes: -1

Related Questions