如何用Jolt转换拆分含逗号分隔值的JSON为多条记录？

输入JSON #

{"DeptID": "339283","EmpID": "280908,280909","EmpCode": "123,31424","Manager":"James,Thiyags"}

期望输出JSON #

[{"DeptID": "339283","EmpID": "280908","EmpCode": "123","Manager":"James"},{"DeptID": "339283","EmpID": "280909","EmpCode": "31424","Manager":"Thiyags"}]

Jolt转换规范 #

[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "EmpID": "=split(',',@(1,EmpID))",
      "EmpCode": "=split(',',@(1,EmpCode))",
      "Manager": "=split(',',@(1,Manager))"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "EmpID": {
        "*": {
          "@2,DeptID": "[&1].DeptID",
          "@": "[&1].EmpID",
          "@2,EmpCode[&1]": "[&1].EmpCode",
          "@2,Manager[&1]": "[&1].Manager"
        }
      }
    }
  }
]

转换步骤说明 #

1. 拆分逗号分隔字段：通过modify-overwrite-beta操作调用split函数，将EmpID、EmpCode、Manager这三个字段的逗号分隔值拆分为数组，得到中间结构：

   {
     "DeptID": "339283",
     "EmpID": ["280908", "280909"],
     "EmpCode": ["123", "31424"],
     "Manager": ["James", "Thiyags"]
   }
   

2. 生成多条记录：利用shift操作遍历EmpID数组的每个索引（*匹配所有索引），为每个索引创建一条独立记录：
   • @2,DeptID：向上两层获取DeptID的固定值，赋值给每条记录的对应字段
   • @：直接取当前EmpID数组元素的值
   • @2,EmpCode[&1]和@2,Manager[&1]：通过&1引用当前索引，匹配对应位置的数组元素值，保证字段一一对应

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