如何在MongoDB中对关联后的数据实现多层嵌套分组查询？

实现方案 #

首先需要说明：你给出的期望输出中users字段是包含重复user键的对象，这不符合JSON语法规范，相同键会被后出现的内容覆盖，因此下面的实现会把users调整为对象数组格式，如果你确实需要特殊结构可以再调整字段输出逻辑。

以下是MongoDB场景下的聚合实现代码，直接执行即可得到嵌套结构的结果：

db.collection.aggregate([
  // 第一层分组：按请求+组织维度聚合，收拢同组织下的所有用户数据
  {
    $group: {
      _id: {
        Request: "$Request",
        TotalRequests: "$TotalRequests",
        Organization: "$Organization",
        TotalRequestsByOrganization: "$TotalRequestsByOrganization"
      },
      users: {
        $push: {
          user: "$user",
          TotalRequestsByUserInOrganization: "$TotalRequestsByUserInOrganization"
        }
      }
    }
  },
  // 第二层分组：按请求维度聚合，收拢同请求下的所有组织数据
  {
    $group: {
      _id: {
        Request: "$_id.Request",
        TotalRequests: "$_id.TotalRequests"
      },
      Organizations: {
        $push: {
          Orgname: "$_id.Organization",
          TotalRequestsByOrganization: "$_id.TotalRequestsByOrganization",
          users: "$users"
        }
      }
    }
  },
  // 调整输出字段结构，匹配需求格式
  {
    $project: {
      _id: 0,
      Request: {
        name: "$_id.Request",
        TotalRequests: "$_id.TotalRequests",
        Organizations: "$Organizations"
      }
    }
  }
])

执行后会得到如下格式的合法JSON结果：

{
  "Request": {
    "name": "first",
    "TotalRequests": 12,
    "Organizations": [
      {
        "Orgname": "A",
        "TotalRequestsByOrganization": 3,
        "users": [
          {"user": "John Smith", "TotalRequestsByUserInOrganization": 1},
          {"user": "John Galt", "TotalRequestsByUserInOrganization": 2}
        ]
      },
      {
        "Orgname": "B",
        "TotalRequestsByOrganization": 7,
        "users": [
          {"user": "Chris Evans", "TotalRequestsByUserInOrganization": 4},
          {"user": "Charlie", "TotalRequestsByUserInOrganization": 3}
        ]
      }
    ]
  }
},
{
  "Request": {
    "name": "second",
    "TotalRequests": 3,
    "Organizations": [
      {
        "Orgname": "B",
        "TotalRequestsByOrganization": 3,
        "users": [
          {"user": "James Anthony", "TotalRequestsByUserInOrganization": 3}
        ]
      }
    ]
  }
}

如果你需要把Organizations从数组改为对象格式，可以在聚合末尾新增$addFields阶段，通过$arrayToObject方法做转换即可。

内容的提问来源于stack exchange，提问作者Anton S.