来自文档和子文档组的 $sum 由 "$author"(MongoDB) [英] $sum from documents and subdocuments group by "$author" (MongoDB)

问题描述

这是我的收藏:

{
        "_id" : 10926400,
        "votes": 131,
        "author": "Jesse",
        "comments" : [
                {
                        "id" : 1,
                        "votes": 31,
                        "author": "Mirek"
                },
                {
                        "id": 2,
                        "votes": 13,
                        "author": "Leszke"
                }
        ]
},
{
        "_id" : 10926401,
        "votes": 75,
        "author": "Mirek",
        "comments" : [
                {
                        "id" : 1,
                        "votes": 17,
                        "author": "Jesse"
                },
                {
                        "id": 2,
                        "votes": 29,
                        "author": "Mirek"
                }
        ]
}

我想要每个author

预期输出(sort $votes: -1):

"Mirek" total votes: 31 + 75 + 29 = 135

"Jesse" total votes: 131 + 17 = 148

"Leszke total votes: 13

推荐答案

不是立即可见，但可能.您需要在这里做的是将您的顶级文档与评论数组结合起来，而不是复制它.这是一种首先将内容作为两个数组加入一个单一数组的方法，然后 $unwind 对内容进行分组:

Not immediately visible but possible. What you need to do here is combine your top level document with the array of comments without duplicating it. Here's an approach to first join the content as two arrays into a singular array, then $unwind to group the content:

db.collection.aggregate([
    { "$group": {
        "_id": "$_id",
        "author": { 
            "$addToSet": {
                "id": "$_id",
                "author": "$author",
                "votes": "$votes"
            }
        },
        "comments": { "$first": "$comments" }
    }},
    { "$project": {
        "combined": { "$setUnion": [ "$author", "$comments" ] }
    }},
    { "$unwind": "$combined" },
    { "$group": {
        "_id": "$combined.author",
        "votes": { "$sum": "$combined.votes" }
    }},
    { "$sort": { "votes": -1 } }
])

给出输出:

{ "_id" : "Jesse", "votes" : 148 }
{ "_id" : "Mirek", "votes" : 135 }
{ "_id" : "Leszke", "votes" : 13 }

<小时>

即使跳过第一个 $group 阶段并以不同的方式制作组合数组:

---

Even as skipping the first $group stage and making a combined array a different way:

db.collection.aggregate([
    { "$project": {
        "combined": { 
            "$setUnion": [
                { "$map": {
                    "input": { "$literal": ["A"] },
                    "as": "el",
                    "in": { 
                        "author": "$author",
                        "votes": "$votes"
                    }
                }},
                "$comments"
            ] 
        }
    }},
    { "$unwind": "$combined" },
    { "$group": {
        "_id": "$combined.author",
        "votes": { "$sum": "$combined.votes" }
    }},
    { "$sort": { "votes": -1 } }
])

<小时>

那些使用运算符，例如 $setUnion 甚至 $map 从 MongoDB 2.6 开始引入.这使得它更简单，但仍然可以在缺少这些运算符的早期版本中完成，遵循基本相同的原则:

---

Those use operators such as $setUnion and even $map which were introduced as of MongoDB 2.6. This makes it simplier, but it can still be done in earlier versions lacking those operators, following much the same principles:

db.collection.aggregate([
    { "$project": {
        "author": 1,
        "votes": 1,
        "comments": 1,
        "type": { "$const": ["A","B"] }
    }},
    { "$unwind": "$type" },
    { "$unwind": "$comments" },
    { "$group": { 
        "_id": {
          "$cond": [
              { "$eq": [ "$type", "A" ] },
              { 
                  "id": "$_id", 
                  "author": "$author",
                  "votes": "$votes"
              },
              "$comments"
          ]
        }
    }},
    { "$group": {
        "_id": "$_id.author",
        "votes": { "$sum": "$_id.votes" }
    }},
    { "$sort": { "votes": -1 } }
])

$const 没有记录，但存在于所有存在聚合框架的 MongoDB 版本中(从 2.2 开始).MongoDB 2.6 引入 $literal 本质上链接到相同的底层代码.这里有两种情况使用它为数组提供模板元素，或者引入一个数组来展开以在两个操作之间提供二元选择".

The $const is undocumented but present in all versions of MongoDB where the aggregation framework is present ( from 2.2 ). MongoDB 2.6 Introduced $literal which essentially links to the same underlying code. It's been used in two cases here to either provide a template element for an array, or as introducing an array to unwind in order to provide a "binary choice" between two actions.

这篇关于来自文档和子文档组的 $sum 由 &amp;quot;$author&amp;quot;(MongoDB)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！