代码之家 › 专栏 › 技术社区 › Nicholas Tsaoucis

mongodb聚合中按id对3个数组进行分组

mongodb

Nicholas Tsaoucis · 技术社区 · 3 年前

我正在使用 $facet 在一个集合中进行几个分组和计算,我计划用它来构建视图。我让组的3个部分独立工作,但现在我想通过_id将所有返回的对象数组合并到一个数组中。

这是我正在使用的模式。任务数组存在于另一个文档中。

   "tasks": [
      {
        "resource": {
          "uuid": "string"
        },
        "supervisor_assessment": {
          "datetime": "string",
          "rating": 0
        },
        "manager_assessment": {
          "datetime": "string",
          "rating": 0
        }
      }
   ]

到目前为止我的合计

[
  {
    '$unwind': {
      'path': '$tasks'
    }
  }, {
    '$facet': {
      'vmratings': [
        {
          '$group': {
            '_id': {
              'resource': '$tasks.resource.uuid', 
              'rating': '$tasks.venue_manager_assessment.rating'
            }, 
            'count': {
              '$sum': 1
            }, 
            'vmratingavg': {
              '$avg': '$tasks.venue_manager_assessment.rating'
            }
          }
        }, {
          '$group': {
            '_id': '$_id.resource', 
            'vmcounts': {
              '$push': {
                'rating': '$_id.rating', 
                'count': '$count'
              }
            }
          }
        }
      ], 
      'sratings': [
        {
          '$group': {
            '_id': {
              'resource': '$tasks.resource.uuid', 
              'rating': '$tasks.supervisor_assessment.rating'
            }, 
            'count': {
              '$sum': 1
            }
          }
        }, {
          '$group': {
            '_id': '$_id.resource', 
            'scounts': {
              '$push': {
                'rating': '$_id.rating', 
                'count': '$count'
              }
            }
          }
        }
      ], 
      'totalAvg': [
        {
          '$group': {
            '_id': '$tasks.resource.uuid', 
            'vmratingavg': {
              '$avg': '$tasks.venue_manager_assessment.rating'
            }, 
            'sratingavg': {
              '$avg': '$tasks.supervisor_assessment.rating'
            }, 
            'sratingcount': {
              '$sum': '$tasks.supervisor_assessment.rating'
            }, 
            'vmratingcount': {
              '$sum': '$tasks.venue_manager_assessment.rating'
            }
          }
        }
      ]
    }
  }
]

这给了我这样的输出(关于我的测试数据)

{
    "vmratings": [
        {
            "_id": "string2",
            "vmcounts": [
                {
                    "rating": 2,
                    "count": 53
                },
                {
                    "rating": 3,
                    "count": 1
                }
            ]
        },
        {
            "_id": "string",
            "vmcounts": [
                {
                    "rating": 2,
                    "count": 53
                },
                {
                    "rating": 1,
                    "count": 1
                }
            ]
        }
    ],
    "sratings": [
        {
            "_id": "string2",
            "scounts": [
                {
                    "rating": 1,
                    "count": 53
                },
                {
                    "rating": 4,
                    "count": 1
                }
            ]
        },
        {
            "_id": "string",
            "scounts": [
                {
                    "rating": 5,
                    "count": 1
                },
                {
                    "rating": 1,
                    "count": 53
                }
            ]
        }
    ],
    "totalAvg": [
        {
            "_id": "string2",
            "vmratingavg": 2.0185185185185186,
            "sratingavg": 1.0555555555555556,
            "sratingcount": 57,
            "vmratingcount": 109
        },
        {
            "_id": "string",
            "vmratingavg": 1.9814814814814814,
            "sratingavg": 1.0740740740740742,
            "sratingcount": 58,
            "vmratingcount": 107
        }
    ]
}

我正试图完成聚合的最后一步,将这三个数组(totalAvg、sratings、vmratings)合并为一个以_id为键的对象数组。

0 回复 | 直到 3 年前

YuTing 3 年前

db.collection.aggregate([
  {
    $project: {
      result: {
        $map: {
          input: { $range: [ 0, { $size: "$vmratings" } ] },
          as: "x",
          in: {
            $let: {
              vars: { id: { $arrayElemAt: [ "$vmratings._id", "$$x" ] } },
              in: {
                _id: "$$id",
                vmratings: {
                  $first: {
                    $filter: {
                      input: "$vmratings",
                      as: "q",
                      cond: { $eq: [ "$$q._id", "$$id" ] }
                    }
                  }
                },
                sratings: {
                  $first: {
                    $filter: {
                      input: "$sratings",
                      as: "q",
                      cond: { $eq: [ "$$q._id", "$$id" ] }
                    }
                  }
                },
                totalAvg: {
                  $first: {
                    $filter: {
                      input: "$totalAvg",
                      as: "q",
                      cond: { $eq: [ "$$q._id", "$$id" ] }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$result"
  },
  {
    $replaceWith: {
      $mergeObjects: [ "$result.sratings", "$result.totalAvg", "$result.vmratings" ]
    }
  }
])

mongoplayground