MongoDB 聚合 - 匹配数组中的值

MongoDB Aggregation - match if value in array

提问by eml002

I have a collection that I'm performing an aggregation on and I've basically gotten it down to

我有一个正在执行聚合的集合，我基本上已经把它归结为

{array:[1,2,3], value: 1},
{array:[1,2,3], value: 4}

How would I perform an aggregation match to check if the value is in the array? I tried using {$match: {"array: {$in: ["$value"]}}}but it doesn't find anything.

我将如何执行聚合匹配以检查该值是否在数组中？我尝试使用{$match: {"array: {$in: ["$value"]}}}但它没有找到任何东西。

I would want the output (if using the above as an example) to be:

我希望输出（如果使用上面的例子）是：

{array:[1,2,3], value:1}

采纳答案by Sylvain Leroux

A slight variation based on @chridam's answer:

根据@chridam 的回答略有不同：

db.test.aggregate([
    { "$unwind": "$array" },
    { "$group": {
                  _id: { "_id": "$_id", "value": "$value" },
                  array: { $push: "$array" },
                  mcount: { $sum: {$cond: [{$eq: ["$value","$array"]},1,0]}}
                }
    },
    { $match: {mcount: {$gt: 0}}},
    { "$project": { "value": "$_id.value", "array": 1, "_id": 0 }}
])

The idea is to $unwindand $groupback the array, counting in mcountthe number of items matching the value. After that, a simple $matchon mcount > 0will filter out unwanted documents.

我们的想法是$unwind和$group背面的阵列，计数在mcount匹配值的项数。之后，一个简单的$matchonmcount > 0将过滤掉不需要的文件。

回答by Blakes Seven

As stated, $whereis a good option where you do not need to continue the logic in the aggregation pipeline.

如上所述，这$where是一个不错的选择，您不需要在聚合管道中继续逻辑。

But if you do then use $redact, with $mapto transform the "value" into an array and use of $setIsSubSetto compare. It is the fastest way to do this since you do not need to duplicate documents using $unwind:

但是，如果您这样做，则使用$redact, with$map将“值”转换为数组并使用 of$setIsSubSet进行比较。这是最快的方法，因为您不需要使用$unwind以下方法复制文档：

db.collection.aggregate([
   { "$redact": {
       "$cond": {
           "if": { "$setIsSubset": [
                { "$map": {
                    "input": { "$literal": ["A"] },
                    "as": "a",
                    "in": "$value"
                }},
                "$array"
           ]},
           "then": "$$KEEP",
           "else": "$$PRUNE"
       }
   }}
])

The $redactpipeline operator allows the proccessing of a logical condition within $condand uses the special operations $$KEEPto "keep" the document where the logical condition is true or $$PRUNEto "remove" the document where the condition was false.

该$redact管道运营商允许的范围内的逻辑条件的等待处理$cond，并使用特别行动$$KEEP“养”，其中的逻辑条件为真或文档$$PRUNE为“删除”，其中条件是假文件。

This allows it to work like $projectwith a subsequent $match, but in a single pipeline stage which is more efficient.

这允许它像$project随后的$match，但在更有效的单个管道阶段中工作。

Considering these are native coded operators and not JavaScript then it is likely "the" fastest way to perform your match. So provided you are using a MongoDB 2.6 version or above, then this is the way you should be doing it to compare these elements in your document.

考虑到这些是本机编码的运算符而不是 JavaScript，那么它可能是执行匹配的“最快”方式。因此，如果您使用的是 MongoDB 2.6 或更高版本，那么您应该采用这种方式来比较文档中的这些元素。

回答by Sagar Veeram

You can use aggregation expression in regular query in 3.6 version.

3.6 版本可以在正则查询中使用聚合表达式。

db.collection_name.find({"$expr": {"$in": ["$value", "$array"]}})

Using Aggregation:

使用聚合：

You can use $match + $exprin current 3.6version.

您可以$match + $expr在当前3.6版本中使用。

db.collection_name.aggregate({"$match": {"$expr": {"$in": ["$value", "$array"]}}})

You can try $redact + $inexpression in 3.4version.

您可以$redact + $in在3.4版本中尝试表达式。

db.collection_name.aggregate({
  "$redact": {
    "$cond": [
      {
        "$in": [
          "$value",
          "$array"
        ]
      },
      "$$KEEP",
      "$$PRUNE"
    ]
  }
})

回答by chridam

A more efficient approach would involve a single pipeline that uses the $redactoperator as follows:

一种更有效的方法将涉及使用$redact运算符的单个管道，如下所示：

db.collection.aggregate([
    { 
        "$redact": {
            "$cond": [
                { 
                    "$setIsSubset": [ 
                        ["$value"],
                        "$array"  
                    ] 
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

---

---

For earlier versions of MongoDB that do not support $redact(versions < 2.6) then consider this aggregation pipeline that uses the $unwindoperator:

对于不支持$redact（版本 < 2.6）的早期 MongoDB 版本，请考虑使用该$unwind运算符的聚合管道：

db.collection.aggregate([
    { "$unwind": "$array" },
    {
        "$project": {
            "isInArray": {
                "$cond": [
                    { "$eq": [ "$array", "$value" ] },
                    1,
                    0
                ]
            },
            "value": 1,
            "array": 1
        }
    },
    { "$sort": { "isInArray": -1 } },
    {
        "$group": {
            "_id": {
                "_id": "$_id",
                "value": "$value"
            },
            "array": { "$push": "$array" },
            "isInArray": { "$first": "$isInArray" }
        }
    },
    { "$match": { "isInArray": 1 } },
    { "$project": { "value": "$_id.value", "array": 1, "_id": 0 } }
])

回答by Ralph

A little late to answer but this presents another solution:

回答有点晚，但这提出了另一种解决方案：

By using addFields and match separately, this gives more flexibility than the redact. You can expose several fields and then use other matching logic together based on the results.

通过分别使用 addFields 和 match，这比 redact 提供了更大的灵活性。您可以公开多个字段，然后根据结果一起使用其他匹配逻辑。

db.applications.aggregate([
    {$addFields: {"containsValueInArray": {$cond:[{$setIsSubset: [["valueToMatch"], "$arrayToMatchIn"]},true,false]}}},
    {$match: {"containsValueInArray":true}}
]);

回答by styvane

You can use $whereif aggregation not required

$where如果不需要聚合，您可以使用

db.collection.find({ $where: function(){ 
    return (this.array.indexOf(this.value) !== -1)}
})

回答by Richard Mao

Try the combination of $eq and $setIntersection

试试 $eq 和 $setIntersection 的组合

{$group :{
  _id: "$id",
  yourName :  { $sum:
  { $cond :[
       {$and : [
          {$eq:[{$setIntersection : ["$someArrayField", ["$value"]]  },["$value"]]}
         ]
      },1,0]
  }

} }

} }

回答by F.H.

i prefer without grouping, there's an easy approach since v.3.2

我更喜欢不分组，自 v.3.2 以来有一个简单的方法

...aggregate([
      {
        $addFields: {
          arrayFilter: {
            $filter: {
              input: '$array',
              as: 'item',
              cond: ['$$item', '$value']
            }
          }
        }
      },
      {
        $unwind: '$arrayFilter'
      },
      {
        $project: {
          arrayFilter: 0
        }
      }
    ]);

1. Add a temporary filter field
2. $unwind on the resulting array (pipeline results with empty arrays get removed)
3. (optional) remove filter field from result via project

1. 添加临时过滤字段
2. $unwind 在结果数组上（删除带有空数组的管道结果）
3. （可选）通过项目从结果中删除过滤器字段

回答by vajid khokhar

You can do it with simple $project & $match

你可以用简单的 $project & $match

db.test.aggregate([{
$project: {
              arrayValue: 1,
              value: 1,
              "has_same_value" : { $in: ["$value", "$arrayValue"] }
          }
},
{
   $match: {has_same_value: true}
},
{
  $project: {has_same_value: 0}
}])

回答by Kevin007

      "$match": { "name": { "$in":["Rio","Raja"] }} }])