如何在MongoDB中基于指定key分组查询员工salary的最大最小值

我需要基于currency字段分组，获取员工salary的最大值与最小值，每位员工都有对应币种的薪资区间，要求返回的所有城市、国家、员工信息均唯一。目前使用聚合函数min和max只能查询到整体薪资的最大最小值，无法按currency字段分组计算。

示例数据 #

[
  {
    "id": "1",
    "emp_name": "emp1",
    "data": [
      {
        "emp_country": "country1",
        "emp_city": "city1",
        "salary": [
          {
            "currency": "INR",
            "amount": 5000
          },
          {
            "currency": "INR",
            "amount": 600
          },
          {
            "currency": "MXN",
            "amount": 400
          }
        ]
      },
      {
        "emp_country": "country1",
        "emp_city": "city2",
        "salary": [
          {
            "currency": "DOLLER",
            "amount": 5000
          },
          {
            "currency": "DOLLER",
            "amount": 200
          },
          {
            "currency": "MXN",
            "amount": 400
          }
        ]
      }
    ]
  },
  {
    "id": "2",
    "emp_name": "emp2",
    "data": [
      {
        "emp_country": "country2",
        "emp_city": "city2",
        "salary": [
          {
            "currency": "INR",
            "amount": 5000
          },
          {
            "currency": "MXN",
            "amount": 200
          },
          {
            "currency": "MXN",
            "amount": 400
          }
        ]
      }
    ]
  },
  {
    "id": "3",
    "emp_name": "emp3",
    "data": [
      {
        "emp_country": "country1",
        "emp_city": "city1",
        "salary": [
          {
            "currency": "MXN",
            "amount": 400
          }
        ]
      }
    ]
  },
  {
    "id": "4",
    "emp_name": "emp4",
    "data": [
      {
        "emp_country": "country1",
        "emp_city": "city2",
        "salary": [
          {
            "currency": "DOLLER",
            "amount": 200
          }
        ]
      }
    ]
  }
]

预期输出 #

要求城市、国家、员工姓名唯一，薪资按currency分组展示最大值和最小值，输出结构如下：

[
  {
    "emp_city": "city1",
    "emp_country": "country1",
    "emp_name": "emp1",
    "emp_salary": [{
      "currency": "INR",
      "max": 5000,
      "min": 600
    },
    {
      "currency": "MXN",
      "max": 400,
      "min": 400
    }]
  },
  {
    "emp_city": "city2",
    "emp_country": "country1",
    "emp_name": "emp1",
    "emp_salary":[ {
      "currency": "DOLLER",
      "max": 5000,
      "min": 200
    },
    {
      "currency": "MXN",
      "max": 400,
      "min": 400
    }]
  },
  {
    "emp_city": "city2",
    "emp_country": "country2",
    "emp_name": "emp2",
    "emp_salary": [{
      "currency": "INR",
      "max": 5000,
      "min": 5000
    },
    {
      "currency": "MXN",
      "max": 400,
      "min": 200
    }]
  },
  {
    "emp_city": "city1",
    "emp_country": "country1",
    "emp_name": "emp3",
    "emp_salary": [{
      "currency": "MXN",
      "max": 400,
      "min": 400
    }]
  },
  {
    "emp_city": "city2",
    "emp_country": "country1",
    "emp_name": "emp4",
    "emp_salary": [{
      "currency": "DOLLER",
      "max": 200,
      "min": 200
    }]
  }
]

实现方案（以MongoDB聚合查询为例） #

核心思路是先展开所有嵌套数组，再按维度两次分组完成统计：

db.集合名.aggregate([
  // 展开第一层data数组
  { $unwind: "$data" },
  // 展开第二层salary数组
  { $unwind: "$data.salary" },
  // 第一次分组：按员工、国家、城市、币种分组，计算对应币种的薪资最大最小值
  {
    $group: {
      _id: {
        emp_name: "$emp_name",
        emp_country: "$data.emp_country",
        emp_city: "$data.emp_city",
        currency: "$data.salary.currency"
      },
      max: { $max: "$data.salary.amount" },
      min: { $min: "$data.salary.amount" }
    }
  },
  // 第二次分组：按员工、国家、城市分组，将同组的币种薪资统计合并为数组
  {
    $group: {
      _id: {
        emp_name: "$_id.emp_name",
        emp_country: "$_id.emp_country",
        emp_city: "$_id.emp_city"
      },
      emp_salary: {
        $push: {
          currency: "$_id.currency",
          max: "$max",
          min: "$min"
        }
      }
    }
  },
  // 调整输出字段格式，符合预期结构
  {
    $project: {
      _id: 0,
      emp_name: "$_id.emp_name",
      emp_country: "$_id.emp_country",
      emp_city: "$_id.emp_city",
      emp_salary: 1
    }
  }
])

如果是前端JS内存计算，逻辑和上述聚合逻辑一致，先遍历所有嵌套数据打平，再按维度分组统计即可。

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