如何在MongoDB中按货币类型查询员工薪资的最大值与最小值

需要按「员工名称、所属国家、所属城市」维度聚合数据，每个维度组合仅返回一条唯一明细，同时对每个组合下的薪资按不同货币类型分别统计最大值和最小值。常规的全局min、max聚合只能返回整体薪资的极值，无法按货币类型拆分统计。

样例输入数据 #

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

预期输出结构 #

[
  {
    "emp_city": "city1",
    "emp_country": "country1",
    "emp_name": "emp1",
    "emp_salary": [{
      "currency": "INR",
      "max": 5000,
      "min": 600
    },
    {
      "currency": "DOLLER",
      "max": 600,
      "min": 200
    },
    {
      "currency": "MXN",
      "max": 400,
      "min": 200
    }]
  },
  {
    "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
    }]
  }
]

Python实现代码 #

from collections import defaultdict
import json

def calc_salary_range(raw_data):
    # 用(员工名, 国家, 城市)作为分组键去重
    group_dict = defaultdict(lambda: defaultdict(list))
    for emp in raw_data:
        emp_name = emp["emp_name"]
        for data_item in emp["data"]:
            country = data_item["emp_country"]
            city = data_item["emp_city"]
            group_key = (emp_name, country, city)
            # 拆分货币类型和对应薪资
            for sal_item in data_item["salary"]:
                for currency, amount in sal_item.items():
                    group_dict[group_key][currency].append(amount)
    # 组装输出结果
    result = []
    for (name, country, city), sal_map in group_dict.items():
        emp_salary = []
        for currency, amounts in sal_map.items():
            emp_salary.append({
                "currency": currency,
                "max": max(amounts),
                "min": min(amounts)
            })
        result.append({
            "emp_city": city,
            "emp_country": country,
            "emp_name": name,
            "emp_salary": emp_salary
        })
    return result

# 调用示例
if __name__ == "__main__":
    # 可直接替换为你的实际数据
    with open("emp_data.json", "r", encoding="utf-8") as f:
        raw_data = json.load(f)
    res = calc_salary_range(raw_data)
    print(json.dumps(res, ensure_ascii=False, indent=2))

MongoDB聚合查询实现 #

如果数据存储在MongoDB中，可直接用以下聚合管道完成统计：

db.员工集合名.aggregate([
  // 展开第一层嵌套的data数组
  {$unwind: "$data"},
  // 展开第二层嵌套的salary数组
  {$unwind: "$data.salary"},
  // 将货币对象拆分为键值对数组
  {$addFields: {
    "currency_info": {$objectToArray: "$data.salary"},
  }},
  {$unwind: "$currency_info"},
  // 按员工名、国家、城市、货币类型第一次分组，统计对应货币的极值
  {$group: {
    _id: {
      emp_name: "$emp_name",
      emp_country: "$data.emp_country",
      emp_city: "$data.emp_city",
      currency: "$currency_info.k"
    },
    max_sal: {$max: "$currency_info.v"},
    min_sal: {$min: "$currency_info.v"}
  }},
  // 按员工名、国家、城市第二次分组，组装薪资明细数组
  {$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_sal",
      min: "$min_sal"
    }}
  }},
  // 调整输出字段格式
  {$project: {
    _id: 0,
    emp_name: "$_id.emp_name",
    emp_country: "$_id.emp_country",
    emp_city: "$_id.emp_city",
    emp_salary: 1
  }}
])

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