如何依据EMPLOYEE_ID合并双库员工数据？map函数空值问题求解

问题描述 #

需要将分别从两个数据库获取的员工基本信息和薪资信息，通过EMPLOYEE_ID字段合并为包含完整信息的员工对象数组，解决直接使用map函数时出现的空值问题。

解决方案 #

核心思路是先将薪资数据转换为以EMPLOYEE_ID为键的映射表，再遍历员工基本信息，通过ID快速匹配对应的薪资数据并合并。这种方式查找效率更高，能避免匹配不到的空值问题。

代码实现（JavaScript）

// 模拟从两个数据库获取的原始数据
const rawData = [
  {
    "shortlisted_at": "2021-01-25T01:33:56",
    "gender": "Female",
    "date_of_birth": "1977-09-18T00:00:00",
    "last_name": "sri",
    "interview_scheduled_at": "2023-01-24T21:42:53",
    "attended_test_at": "2021-03-17T20:17:56",
    "offered_joining_letter_at": "2020-12-13T03:43:43",
    "EMPLOYEE_ID": 1,
    "location": "North Jenniferfort",
    "date_of_hire": "2021-04-13T00:00:00",
    "applied_for_job_at": "2022-02-18T15:50:07",
    "first_name": "rama",
    "job_title": "Engineer, water",
    "email": "ellisjames@example.net"
  },
  {
    "shortlisted_at": "2022-05-19T02:28:55",
    "gender": "Male",
    "date_of_birth": "1977-09-18T00:00:00",
    "last_name": "Gonzalez",
    "interview_scheduled_at": "2023-01-24T21:42:53",
    "attended_test_at": "2021-03-17T20:17:56",
    "offered_joining_letter_at": "2020-12-13T03:43:43",
    "EMPLOYEE_ID": 2,
    "location": "North Jenniferfort",
    "date_of_hire": "2021-04-13T00:00:00",
    "applied_for_job_at": "2022-02-18T15:50:07",
    "first_name": "rama",
    "job_title": "Engineer, water",
    "email": "ellisjames@example.net"
  },
  {
    "SPECIAL_ALLOWANCE": 4848,
    "PT": 2517,
    "UNIFORM_ALLOWANCE": 3794,
    "HRA_DA": 18108,
    "BASIC_PAY": 15212,
    "TRANSPORT_ALLOWANCE": 3544,
    "SALARY_CREDITED_AT": "2001-05-01T00:00:00",
    "CTC": 38434,
    "PF": 1829,
    "EMPLOYEE_ID": 1,
    "MOBILE_ALLOWANCE": 3417,
    "NETT_PAY": 12199,
    "ESI": 2375,
    "MEDICAL_ALLOWANCE": 1796,
    "EDUCATION_ALLOWANCE": 2285
  },
  {
    "SPECIAL_ALLOWANCE": 1296,
    "PT": 3631,
    "UNIFORM_ALLOWANCE": 1117,
    "HRA_DA": 15751,
    "BASIC_PAY": 15329,
    "TRANSPORT_ALLOWANCE": 1974,
    "SALARY_CREDITED_AT": "2020-07-01T00:00:00",
    "CTC": 41550,
    "PF": 4730,
    "EMPLOYEE_ID": 2,
    "MOBILE_ALLOWANCE": 1973,
    "NETT_PAY": 16175,
    "ESI": 2667,
    "MEDICAL_ALLOWANCE": 3970,
    "EDUCATION_ALLOWANCE": 3631
  }
];

// 1. 拆分原始数据为员工基本信息和薪资信息数组
const employeeInfo = rawData.filter(item => item.hasOwnProperty('first_name'));
const salaryInfo = rawData.filter(item => item.hasOwnProperty('CTC'));

// 2. 创建薪资信息映射表，以EMPLOYEE_ID为键
const salaryMap = salaryInfo.reduce((map, salary) => {
  map[salary.EMPLOYEE_ID] = salary;
  return map;
}, {});

// 3. 合并数据
const mergedEmployees = employeeInfo.map(employee => {
  // 匹配对应ID的薪资信息，合并对象
  return { ...employee, ...salaryMap[employee.EMPLOYEE_ID] };
});

// 输出结果
console.log(JSON.stringify(mergedEmployees, null, 2));

空值问题原因 #

如果直接在employeeInfo.map里遍历salaryInfo查找匹配ID，数据量较大时易出现匹配遗漏，或因遍历顺序问题导致未找到对应数据，从而产生空值。使用映射表可确保每个ID的薪资信息被快速准确匹配，避免该问题。

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