编写JOLT转换含可选嵌套节点的JSON为扁平表记录

需要将包含多层嵌套、可选节点（如Indicator）及多类型嵌套字段（PartnerFunction可为数组或单个对象）的JSON转换为扁平化的数据库记录格式，输入结构中CustomerMaster.Rootnode包含客户基础字段，SalesArea为数组，每个销售区域下可能包含额外属性及伙伴功能信息，需将所有组合展开为独立记录。

输入JSON #

{"CustomerMaster": {"Rootnode": {"KUNNR": "0000028790","NAME1": "NV Solucious","LAND1": "BE","SalesArea": [{"VKORG": "0200","VTWEG": "00","SPART": "00"},{"VKORG": "4932","VTWEG": "00","SPART": "00","AUFSD1": "FT","PartnerFunction": [{"PARVW": "ZP","PARZA": "000","KUNN2": "0000028790"},{"PARVW": "ZS","PARZA": "000","KUNN2": "90010523"}]},{"VKORG": "4932","VTWEG": "10","SPART": "00","Indicator": {"IsDistributionCenter": "X"},"PartnerFunction": {"PARVW": "ZS","PARZA": "000","KUNN2": "90010523"}}]}}}

期望输出（修正笔误后） #

[
  {"KUNNR": "0000028790","NAME1": "NV Solucious","LAND1": "BE","VKORG": "0200","VTWEG": "00","SPART": "00"},
  {"KUNNR" : "0000028790","NAME1" : "NV Solucious","LAND1" : "BE","VKORG" : "4932","VTWEG" : "00","SPART" : "00","AUFSD1": "FT","PARVW": "ZP","PARZA": "000","KUNN2": "0000028790"},
  {"KUNNR" : "0000028790","NAME1" : "NV Solucious","LAND1" : "BE","VKORG" : "4932","VTWEG" : "00","SPART" : "00","AUFSD1": "FT","PARVW": "ZS","PARZA": "000","KUNN2": "90010523"},
  {"KUNNR" : "0000028790","NAME1" : "NV Solucious","LAND1" : "BE","VKORG" : "4932","VTWEG" : "10","SPART" : "00","IsDistributionCenter": "X","PARVW": "ZS","PARZA": "000","KUNN2": "90010523"}
]

实现思路 #

1. 统一嵌套字段格式：将PartnerFunction统一转换为数组（无论原结构是单个对象还是数组），避免遍历逻辑分支。
2. 多层遍历展开：先遍历SalesArea数组，再对每个销售区域下的PartnerFunction数组（或转换后的数组）进行遍历，生成组合记录。
3. 字段传递合并：将客户基础字段、销售区域字段、伙伴功能字段逐层传递并合并到最终记录中。

最终JOLT规则 #

[
  {
    "operation": "shift",
    "spec": {
      "CustomerMaster": {
        "Rootnode": {
          "KUNNR": "common.KUNNR",
          "NAME1": "common.NAME1",
          "LAND1": "common.LAND1",
          "SalesArea": {
            "*": {
              "@(2,common)": "salesAreas[&1].&",
              "VKORG": "salesAreas[&1].VKORG",
              "VTWEG": "salesAreas[&1].VTWEG",
              "SPART": "salesAreas[&1].SPART",
              "AUFSD1": "salesAreas[&1].AUFSD1",
              "Indicator": {
                "IsDistributionCenter": "salesAreas[&2].IsDistributionCenter"
              },
              "PartnerFunction": {
                "*": "salesAreas[&2].PartnerFunction[0].&",
                "[*]": "salesAreas[&3].PartnerFunction[&1].&"
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "salesAreas": {
        "*": {
          "PartnerFunction": {
            "*": {
              "@(2,KUNNR)": "[&3].KUNNR",
              "@(2,NAME1)": "[&3].NAME1",
              "@(2,LAND1)": "[&3].LAND1",
              "@(2,VKORG)": "[&3].VKORG",
              "@(2,VTWEG)": "[&3].VTWEG",
              "@(2,SPART)": "[&3].SPART",
              "@(2,AUFSD1)": "[&3].AUFSD1",
              "@(2,IsDistributionCenter)": "[&3].IsDistributionCenter",
              "*": "[&3].&"
            }
          },
          "*": {
            "@(1,KUNNR)": "[&2].KUNNR",
            "@(1,NAME1)": "[&2].NAME1",
            "@(1,LAND1)": "[&2].LAND1",
            "@(1,VKORG)": "[&2].VKORG",
            "@(1,VTWEG)": "[&2].VTWEG",
            "@(1,SPART)": "[&2].SPART",
            "@(1,AUFSD1)": "[&2].AUFSD1",
            "@(1,IsDistributionCenter)": "[&2].IsDistributionCenter"
          }
        }
      }
    }
  },
  {
    "operation": "remove",
    "spec": {
      "*": {
        "PartnerFunction": ""
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  }
]

规则说明 #

1. 第一步Shift：提取客户基础字段到common节点，遍历SalesArea数组，将每个销售区域的字段与基础字段合并，同时将PartnerFunction统一转换为数组格式，处理Indicator字段的层级展开。
2. 第二步Shift：遍历每个销售区域，若存在PartnerFunction数组则生成对应数量的记录，合并所有字段；若不存在则生成仅包含基础和销售区域字段的记录。
3. Remove操作：清理记录中残留的PartnerFunction节点。
4. Modify操作：自动移除所有null值字段，确保记录简洁。

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