来自文档和子文档组的$ 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的votes和comments.votes的$sum值

预期输出(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(按"$ author"分组) (MongoDB)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！