MongoDB聚合`$ group`和数组名 [英] MongoDB Aggregate `$group` and array names

问题描述

我的mongoDB查询有一种问题(使用aggregate). 我很高兴得到一些线索...

I have a kind of problem with my mongoDB query (using aggregate). And I would be delighted to get some clue...

我的MongoCollection下面:
请注意，KLLS是a，b或c，并且有3个types:processus，work和viewing.

Below my MongoCollection :
Note that KLLS is either a, b or c, and there are 3 types: processus, work and viewing.

{_id: 1, KLLS: "a", action: "A", type: "Processus", date: Date, other:"abc" }
{_id: 2, KLLS: "b", action: "B", type: "Processus", date: Date }
{_id: 3, KLLS: "a", action: "C", type: "Work"     , date: Date, other:"xyz" }
{_id: 4, KLLS: "b", action: "D", type: "Work"     , date: Date }
{_id: 5, KLLS: "a", action: "E", type: "Viewing"  , date: Date }
{_id: 6, KLLS: "b", action: "F", type: "Viewing"  , date: Date }
...

我要通过查询实现的目标:

[ { 
    _id: { KLLS: "a"},
    Processus: [[action: "A", date: "Date", other: "abc"]]
    Work:      [[action: "C", date: "Date"]]
    Viewing:   [[action: "E", date: "Date"]]
  },
  { 
    _id: { KLLS: "b"},
    Processus: [[action: "B", date: "Date"]]
    Work:      [[action: "D", date: "Date", other: "xyz"]]
    Viewing:   [[action: "F", date: "Date"]]
  }
]

此刻，我的(失败)查询是:

db.collection('events').aggregate([
    {$match: {
        KLLS: {$in: something}
    }},
    {$group: {
        _id: {
            klls: "$KLLS",
            type: "$type"
        }, 
        array: { 
            $push: "$$ROOT"
        }
    }}
])

但是，我有一个问题:它产生一个composite id，但我没有3个好听的arrays ...

However, I have one problem : It makes a composite id but I don't have 3 nicely named arrays...

[
 {   
     _id: { KLLS: "a", type: "Processus" },
     array: [[action: "A", date: "Date", other: "abc"]]
 },
 { 
     _id: { KLLS: "a", type: "Work" },
     array: [[action: "C", date: "Date"]]
 },
 { 
     _id: { KLLS: "a", type: "Viewing" },
     array: [[action: "E", date: "Date"]]
 }, 
 { 
     _id: { KLLS: "b", type: "Processus" },
     array: [[action: "B", date: "Date"]]
 },
 { 
     _id: { KLLS: "b", type: "Work" },
     array: [[action: "D", date: "Date", other: "xyz"]]
 }, 
 { 
     _id: { KLLS: "b", type: "Viewing" },
     array: [[action: "F", date: "Date"]]
 },
 ...
]

您知道如何解决此问题吗? 谢谢.

Do you have any idea how to resolve this? Thanks.

********************编辑**********************

让我们想象一下，同一集合还包含:

Let's imagine the very same collection contains also:

{_id: 1, KLLS: "a", action: "A",  type: "Processus", date: Date }
{_id: 2, KLLS: "b", action: "B",  type: "Processus", date: Date }
{_id: 5, KLLS: "a", action: "E",  type: "Viewing"  , date: Date }
{_id: 6, KLLS: "b", action: "F",  type: "Viewing"  , date: Date }
   ...
{_id: 3, KLLS: "a", action: "AB", type: "Work"     , date: Date, key:"123" }
{_id: 4, KLLS: "b", action: "XY", type: "Work"     , date: Date, key: "123" }
{_id: 3, KLLS: "a", action: "AB", type: "Work"     , date: Date, key:"456" }
{_id: 4, KLLS: "b", action: "XY", type: "Work"     , date: Date, key: "456" }
   ...

(请注意，对于类型Work，我得到了另一个key).

(Note that for type Work, I got another key).

感谢您的帮助，此刻，我得到了:

[{ 
    _id: { KLLS: "a"},
    Processus: [   everything is ok    ],
    Viewing:   [   everything is ok    ],
    Details:   [   everything is ok    ],
    Work:      [
        {_id: 3, KLLS: "a", action: "AB", type: "Work", date: Date, key:"123" }
        {_id: 4, KLLS: "b", action: "XY", type: "Work", date: Date, key: "123" }
        {_id: 3, KLLS: "a", action: "AB", type: "Work", date: Date, key:"456" }
        {_id: 4, KLLS: "b", action: "XY", type: "Work", date: Date, key: "456" }
    ]
...

鉴于此，我想要的最终结果是:

[{
    _id: { KLLS: "a"},
    Processus: [   everything is ok    ],
    Viewing:   [   everything is ok    ],
    Details:   [   everything is ok    ],
    Work:      [
        subArrayByKey [ // key = 123
            {_id: 3, KLLS: "a", action: "AB", type: "Work", date: Date, key:"123" },
            {_id: 4, KLLS: "b", action: "XY", type: "Work", date: Date, key: "123" }
        ],
        subArrayByKey [ // key = 456
            {_id: 3, KLLS: "a", action: "AB", type: "Work", date: Date, key:"456" },
            {_id: 4, KLLS: "b", action: "XY", type: "Work", date: Date, key: "456" }
        ]
    ]
...

我尝试做(1)一种nested $group，还尝试(2)将条件添加到您的第一个$ group中以创建composite id(就像我一开始就尝试过的那样).但是$eq让我很尴尬...

I tried doing (1) a kind of nested $group and also (2) to add criteria into your first $group to make a composite id (like I tried at the very beginning). But the $eq is embarrassing me...

{ $match: { something } },
{ $sort: { by date } },
{ $group: { what you wrote } },
{ $group: { a nested $group I tried } }

最后，毫不奇怪，我失败了……返回查询不是空的.. 如果可以的话..你有什么线索...;-)

Finally, and not surprisingly, I failed... the return query is no empty.. If I may.. do you have any clue... ;-)

推荐答案

您可以将$cond运算符与$push结合使用，以将符合条件的值添加到数组中.

You can use the $cond operator with $push to add values to the array that match the condition.

$cond是三元运算符，在下面的示例中，当满足条件时，原始文档将添加到数组中，而当不满足条件时，会将虚拟值false添加到数组中.

$cond is a ternary operator and in the below example the original document is added to the array when the condition is met and a dummy value false is added to the array when the condition is not met.

在最后的$project阶段，使用setDifference运算符删除数组中的虚拟false条目.

In the final $project stage, the dummy false entries in the arrays are removed using the setDifference operator.

db.events.aggregate([
    {"$group": {
         "_id" : "$KLLS",
         "Processus": {
             "$push": { 
                 "$cond": [{"$eq": ["$type", "Processus"]}, "$$ROOT", false]
             }
         },
         "Work": {
             "$push": {
                 "$cond": [{"$eq": ["$type","Work"]}, "$$ROOT", false]
             }
         },
         "Viewing": {
             "$push": {
                 "$cond": [{"$eq" : ["$type","Viewing"]}, "$$ROOT", false]
             }
         },
         "Details": {"$push" : "$other"}
    }},

    {"$project": {
         "_id": 0, 
         "KLLS": "$_id",
         "Processus": {"$setDifference": ["$Processus", [false]]},
         "Work":      {"$setDifference": ["$Work",      [false]]},
         "Viewing":   {"$setDifference": ["$Viewing",   [false]]},
         "Details": 1
    }}
])

此外，要将仅几个字段添加到数组，您可以尝试以下操作.

Also, to add just few fields to the array, you can try something like below.

db.events.aggregate([
    {"$group": {
         "_id": "$KLLS",
         "Processus": {
             "$push": { 
                 "$cond": [{"$eq": ["$type","Processus"]}, {"action": "$action","other": "$other"}, false]
             }
          },
          "Work": {
              "$push": {
                  "$cond": [{"$eq": ["$type","Work"]}, {"action": "$action","other": "$other"}, false]
              }
          },
          "Viewing":{
              "$push": {
                  "$cond": [{"$eq": ["$type","Viewing"]}, {"action": "$action","other": "$other"}, false]
              }
          },
          "Details": {"$push": "$other"}
    }},

    {"$project": { 
         "_id": 0, 
         "KLLS": "$_id",
         "Processus": {"$setDifference": ["$Processus", [false]]},
         "Work":      {"$setDifference": ["$Work",      [false]]},
         "Viewing":   {"$setDifference": ["$Viewing",   [false]]},
         "Details": 1
    }}
])

这篇关于MongoDB聚合`$ group`和数组名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！