MongoDB-具有嵌套字段的组组合键 [英] MongoDB - group composite key with nested fields

问题描述

我在mongodb中有此文档:

I have this document in mongodb:

{
"_id":"26/04/2015 09:50",
"reservations":130,
"Event_types":[
   {
     "type":"Party",
     "events":[
        {
           "eventName":"After Party",
           "total_count":130,
           "by":[
              {
                 "siteName":"club8",
                 "countArray":[
                    {
                       "bucket":"default",
                       "value":40
                    }
                 ]
              },
              {
                 "siteName":"PostParty",
                 "countArray":[
                    {
                       "bucket":"1",
                       "value":70
                    },
                    {
                       "bucket":"2",
                       "value":20
                    }
                 ]
              }
           ]
        }
      ]
   }
 ]
}

我在寻找什么

我希望对值"字段求和并按以下字段分组:

I wish to sum the "value" field and group by these fields:

1. 类型
2. eventName
3. siteName

因此，对于我拥有的文档，我希望得到它:

So for the document I have I would expect to get:

1. 对于{{Party，" After Party，" club8}组合，总和为40
2. 对于组合{"Party"，"After Party"，"PostParty"}，总和为90

我尝试过的事情

我尝试将聚合运算符与_id的复合键一起使用:

I've tried using the aggregate operator with a composite key for the _id:

db.testing.aggregate(
{ 
$group : {
    _id : 
    {
    type:'$Event_types.type',
    name: '$Event_types.events.eventName',
    siteName: '$Event_types.events.by.siteName'
    }
    , total : { $sum : '$Event_types.events.by.countArray.value' }
}
});

结果

一个文档，包含3个数组-每个要分组的值对应一个. "siteName"数组包含"siteName"可用的2个值. 总计"似乎没有汇总任何内容，它只出现一次-我希望在文档中每个"SiteName"值旁边看到它.

one document, with 3 arrays - one for every value I wish to group by. The "siteName" array contains the 2 values available for "siteName". The "total" doesn't seem to sum up anything, and it appears only once - I expected to see it next to each "SiteName" value in the document.

 {
   "_id":{
      "type":[
         "Party"
      ],
      "name":[
         [
            "After Party"
         ]
      ],
      "siteName":[
         [
            [
               "club8",
               "PostParty"
            ]
         ]
      ]
   },
   "total":0
}

我使用聚合"方式错误还是我使用的架构不适合我的目标? 谢谢.

Am I using "aggregate" the wrong way or is the schema I'm using not fit for my goal? Thank you.

推荐答案

您需要首先应用 $unwind 运算符，以便您可以使用

You need to first apply the $unwind operator on all the arrays so that you can do the aggregation calculations with the $group operator later in the pipeline stages. In the end you will end up with an aggregation pipeline like this:

db.testing.aggregate([
    { "$unwind": "$Event_types" },
    { "$unwind": "$Event_types.events" },
    { "$unwind": "$Event_types.events.by" },
    { "$unwind": "$Event_types.events.by.countArray" },
    {
        "$group": {
            "_id": {
                "type": "$Event_types.type",
                "name": "$Event_types.events.eventName",
                "siteName": "$Event_types.events.by.siteName"
            },
            "total": { 
                "$sum": "$Event_types.events.by.countArray.value"
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "type": "$_id.type",
            "name": "$_id.name",
            "siteName": "$_id.siteName",
            "total": 1
        }
    }
]);

输出

/* 1 */
{
    "result" : [ 
        {
            "total" : 90,
            "type" : "Party",
            "name" : "After Party",
            "siteName" : "PostParty"
        }, 
        {
            "total" : 40,
            "type" : "Party",
            "name" : "After Party",
            "siteName" : "club8"
        }
    ],
    "ok" : 1
}

这篇关于MongoDB-具有嵌套字段的组组合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！