如何在 MongoDB 集合中查找与给定条件匹配的文档和单个子文档 [英] 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
            }
    ]
}

那么，您能否给我一个建议，我如何查询 MongoDB 以检索所有产品，其中只有一个项目，其日期等于我作为参数传递给查询的日期.

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.

31.08.2014"的结果必须是:

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

推荐答案

您正在寻找的是 位置 $ 运算符和投影".对于单个字段，您需要使用点表示法"匹配所需的数组元素，对于多个字段使用 $elemMatch:

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

或 $elemMatch 用于多个匹配字段:

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

或者可能是自 MongoDB 2.6 以来更短/更快的形式，其中您的项目数组包含唯一条目:

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

或者可能使用 $redact，但有点做作:

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

更现代的，你会使用 $filter:

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

在多个条件下，$filter中的$elemMatch和$and:

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

所以这仅取决于您是否总是期望单个元素匹配或多个元素，然后哪种方法更好.但在可能的情况下，.find() 方法通常会更快，因为它没有其他操作的开销，而在最后的表单中，这些操作根本不会落后那么远.

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.

作为旁注，您的日期"表示为字符串，这不是一个很好的主意.考虑将这些更改为正确的 Date 对象类型，这将对您将来有很大帮助.

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.

这篇关于如何在 MongoDB 集合中查找与给定条件匹配的文档和单个子文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！