时间戳

首页 > 解决方案 > 按字段值聚合多个数组

mongodb - 按字段值聚合多个数组

问题描述

使用如下所示的文档，我正在尝试聚合数据，因此我的最终输出是每个用户received和sent值的总和。

文档

{
    "_id" : 1,
    "received" : [
        { "name" : "david", "value" : 15 }, 
        { "name" : "sarah", "value" : 10 }, 
        { "name" : "sarah", "value" : 15 }
    ],
    "sent" : [ 
        { "name" : "david", "value" : 10 }, 
        { "name" : "sarah", "value" : 20 }, 
        { "name" : "david", "value" : 15 }
    ]
}

期望的结果（或类似的）

{
    "name": "david",
    "received": 15,
    "sent": 25
},
{
    "name": "sarah",
    "received": 25,
    "sent": 20
}

我试图放松接收和发送，但我最终得到了很多重复，老实说，我不知道是否可以在不先将数据集带入我的客户端的情况下创建这种输出。

对 StackOverflow 的进一步搜索使我找到了mongodb 聚合多个数组来提供合适的答案。我已将此标记为重复。

我按照上述帖子创建的最终解决方案如下；

[
  {
    '$addFields': {
      'received.type': 'received', 
      'sent.type': 'sent'
    }
  }, {
    '$project': {
      'movements': {
        '$concatArrays': [
          '$received', '$sent'
        ]
      }
    }
  }, {
    '$unwind': {
      'path': '$movements'
    }
  }, {
    '$project': {
      'name': '$movements.name', 
      'type': '$movements.type', 
      'value': '$movements.value'
    }
  }, {
    '$group': {
      '_id': '$name', 
      'sent': {
        '$sum': {
          '$cond': {
            'if': {
              '$eq': [
                '$type', 'sent'
              ]
            }, 
            'then': '$value', 
            'else': 0
          }
        }
      }, 
      'received': {
        '$sum': {
          '$cond': {
            'if': {
              '$eq': [
                '$type', 'received'
              ]
            }, 
            'then': '$value', 
            'else': 0
          }
        }
      }
    }
  }
]

标签： mongodbmongodb-query

解决方案

在顶部添加$match阶段以过滤文档

  • $facet通过在同一个文档上发送和接收来计算两个不同的结果
  • $group合并前一阶段的发送和接收字段
  • $unwind&$unwind展开合并的数组数组
  • $replaceRoot用 byBoth 替换 root
  • $group合并结果
  • $project仅过滤和投影必填字段

聚合管道

db.ttt.aggregate([
    {$facet : {
        "byReceived" :[
            {$unwind : "$received"},
            {$group: {_id : "$received.name", received : {$sum : "$received.value"}}}
        ],
        "bySent" :[ 
            {$unwind : "$sent"},
            {$group: {_id : "$sent.name", sent : {$sum : "$sent.value"}}}
        ]
    }},
    {$group: {_id:null, byBoth : {$push :{$concatArrays : ["$bySent", "$byReceived"]}}}},
    {$unwind : "$byBoth"},
    {$unwind : "$byBoth"},
    {$replaceRoot: { newRoot: "$byBoth" }},
    {$group : {_id : "$_id", sent : {$sum : "$sent"}, received : {$sum : "$received"}}},
    {$project : {_id:0, name:"$_id", sent:"$sent", received:"$received"}}
])

结果

{ "name" : "david", "sent" : 25, "received" : 15 }
{ "name" : "sarah", "sent" : 20, "received" : 25 }

推荐阅读