How to find document and single subdocument matchi

问题:

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

回答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"
         ]
    }}
])

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.

回答2:

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.