如何使用Jolt将嵌套数据转换为线性格式数据

需求 #

将嵌套的JSON数据展开为线性格式，每个subList中的元素生成独立的数据条目；每条记录必须包含practice_loc、prac_num、topId、S1、S2五个字段，缺失的字段自动填充为空字符串。

输入数据 #

[
 {
    "practice_loc": 120,
    "prac_num": 234,
    "topId": "t1",
    "subList": [
      {
        "S1": "A1",
        "S2": "B1"
      },
      {
        "S1": "A2"
      }
    ]
  },
 {
    "practice_loc": 334,
    "prac_num": 233,
    "topId": "plumcherry",
    "subList": [
      {
        "S1": "A3"
      }
    ]
  },
{
    "practice_loc": 987,
    "prac_num": 232,
    "topId": "artica",
    "subList": [
      {
        "S1": "A5",
        "S2": "B7"
      }
    ]
  },
{
    "practice_loc": 987,
    "prac_num": 232,
    "topId": "rose",
    "subList": [
      {
      }
    ]
  }
]

期望输出 #

[
{
    "practice_loc": 120,
    "prac_num": 234,
    "topId": "t1",
    "S1": "A1",
    "S2": "B1"
  },
  {
    "practice_loc": 120,
    "prac_num": 234,
    "topId": "t1",
    "S1": "A2",
    "S2":""
 },
{
    "practice_loc": 334,
    "prac_num": 233,
    "topId": "plumcherry",
    "S1": "A3",
    "S2":""    
},
{
    "practice_loc": 987,
    "prac_num": 232,
    "topId": "artica",
    "S1": "A5",
    "S2": "B7"
  },
{
"practice_loc": 987,
    "prac_num": 232,
    "topId": "rose",
     "S1": "",
    "S2":""
}
]

Jolt 转换规则 #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "subList": {
          "*": {
            "@2,practice_loc": "[&1].practice_loc",
            "@2,prac_num": "[&1].prac_num",
            "@2,topId": "[&1].topId",
            "S1": "[&1].S1",
            "S2": "[&1].S2"
          }
        }
      }
    }
  },
  {
    "operation": "default",
    "spec": {
      "*": {
        "S1": "",
        "S2": ""
      }
    }
  }
]

规则说明 #

• Shift 操作：负责将嵌套的subList展开，同时把外层的practice_loc、prac_num、topId字段映射到每个展开后的条目里。@2,xxx代表向上两层获取对应字段值，[&1]用来生成对应索引的数组元素。
• Default 操作：为所有缺失S1或S2的条目填充空字符串，保证每条记录都包含要求的五个字段。

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