MongoDB聚合查询结果移除外层嵌套结构的技术求助

我需要去掉聚合结果的外层嵌套结构，直接返回数据数组，但当前聚合管道最后阶段的处理方式导致使用$mergeObjects时出现“input is an array”错误，使用$concatArrays时出现“input is an object”错误。

现有聚合管道 #

User.aggregate([
    {
      $match: {
        "_id": userId
      }
    },
    {
      $lookup: {
        from: 'clients',
        localField: "clients",
        foreignField: "_id",
        as: "Clients",
        pipeline: [
          {
            $project: {
              "_id": 1,
              "fullName": "$fullName",
              "contracts": {"$last": "$contracts"}
            }
          },
          {
            $project: {
              "_id": 1,
              "fullName": 1,
              "contracts": {
                "type": "$contracts.contType",
                "hours": "$contracts.contHours"
              }
            }
          }
        ]
      }
    },
    {
      $lookup: {
        from: "visits",
        localField: "visits",
        foreignField: "_id",
        as: "Visits",
        pipeline: [
          {
            $match: {
              $and: [
                {"visitStart": {"$gte": new Date(start)}},
                {"visitEnd": {"$lte": new Date(end)}}
              ]
            }
          },
          {
            $project: {
              "_id": 1,
              "client": "$client",
              "hours": "$totalHours",
              "month": {
                "$month": "$visitEnd"
              },
              "year": {
                "$year": "$visitEnd"
              }
            }
          },
          {
            $project: {
              "_id": 1,
              "client": 1,
              "hours": 1,
              "date": {
                $concat: [
                  {
                    $substr: [
                      "$year",
                      0,
                      4
                    ]
                  },
                  "-",
                  {
                    $substr: [
                      "$month",
                      0,
                      2
                    ]
                  }
                ]
              }
            }
          },
          {
            $group: {
              "_id": {
                "date": "$date",
                "client": "$client"
              },
              "hoursCount": {
                $sum: "$hours"
              }
            }
          },
          {
            $group: {
              "_id": "$_id.client",
              "hours": {
                $push: {
                  "date": "$_id.date",
                  "count": "$hoursCount"
                }
              }
            }
          },
          {
            $project: {
              "_id": 0,
              "client": "$_id",
              "hours": {
                $sortArray: {"input": "$hours", "sortBy": {"date": 1}}
              }
            }
          }
        ]
      }
    },
    {
      $project: {
        "_id": 0,
        "Clients": 1,
        "Visits": 1
      }
    },
    {
      $project: {
        "result": {
          $map: {
            "input": "$Clients",
            "as": "client",
            "in": {
              $mergeObjects: [
                "$$client",
                {
                  $arrayElemAt: [
                    {
                      $filter: {
                        "input": "$Visits",
                        "as": "visits",
                        "cond": { $eq: ["$$visits.client", "$$client._id"]}
                      }
                    },
                    0
                  ]
                }
              ]
            }
          }
        } 
      }
    },
])

当前返回结果 #

[
    {
        "result": [
            {
                "_id": "6205a8313fe12d6b4ec354c4",
                "fullName": "Johnny Cochrane",
                "contracts": {
                    "type": "Quarterly",
                    "hours": 120
                },
                "client": "6205a8313fe12d6b4ec354c4",
                "hours": [
                    {
                        "date": "2023-4",
                        "count": 5.51
                    },
                    {
                        "date": "2023-5",
                        "count": 6
                    },
                    {
                        "date": "2023-6",
                        "count": 2
                    }
                ]
            },
            {
                "_id": "6217c1b73fe12d6b4ec3550e",
                "fullName": "Sheila Thompson",
                "contracts": {
                    "type": "Quarterly",
                    "hours": 140
                },
                "client": "6217c1b73fe12d6b4ec3550e",
                "hours": [
                    {
                        "date": "2023-4",
                        "count": 5.5
                    },
                    {
                        "date": "2023-5",
                        "count": 4
                    },
                    {
                        "date": "2023-6",
                        "count": 2
                    }
                ]
            }
        ]
    }
]

尝试过的无效阶段 #

{
      $replaceRoot: {
        newRoot: {
          $mergeObjects: [
            "$$ROOT",
            "$result"
          ]
        }
      }
}

期望结果 #

[
    {
        "_id": "6205a8313fe12d6b4ec354c4",
        "fullName": "Johnny Cochrane",
        "contracts": {
            "type": "Quarterly",
            "hours": 120
        },
        "client": "6205a8313fe12d6b4ec354c4",
        "hours": [
            {
                "date": "2023-4",
                "count": 5.51
            },
            {
                "date": "2023-5",
                "count": 6
            },
            {
                "date": "2023-6",
                "count": 2
            }
        ]
    },
    {
        "_id": "6217c1b73fe12d6b4ec3550e",
        "fullName": "Sheila Thompson",
        "contracts": {
            "type": "Quarterly",
            "hours": 140
        },
        "client": "6217c1b73fe12d6b4ec3550e",
        "hours": [
            {
                "date": "2023-4",
                "count": 5.5
            },
            {
                "date": "2023-5",
                "count": 4
            },
            {
                "date": "2023-6",
                "count": 2
            }
        ]
    }
]

解决方案 #

在现有聚合管道的末尾添加以下两个阶段即可实现需求：

// 展开result数组，将数组中的每个元素转为独立文档
{ $unwind: "$result" },
// 将每个文档的根替换为result中的对象
{ $replaceRoot: { newRoot: "$result" } }

原错误的原因是$result是数组类型，$mergeObjects只能处理对象，无法直接合并数组和对象。通过$unwind将数组拆分为多个单文档后，再用$replaceRoot把每个文档的内容替换为目标对象，就能得到你想要的纯数组结果。

内容的提问来源于stack exchange，提问作者Jowz