如何编写JOLT Spec将多层嵌套JSON转换为扁平JSON？

典型嵌套输入示例 #

{
  "CustomerMaster": {
    "CustomerGeneral": {
      "CustomerId": "C001",
      "CustomerName": "ABC Corp",
      "Country": "US"
    },
    "SalesAreas": [
      {
        "SalesOrg": "S001",
        "DistributionChannel": "D01",
        "Division": "DV1",
        "PartnerFunctions": [
          {
            "PartnerFunction": "SP",
            "PartnerId": "P001",
            "PartnerName": "John Doe"
          },
          {
            "PartnerFunction": "SH",
            "PartnerId": "P002",
            "PartnerName": "Jane Smith"
          }
        ]
      },
      {
        "SalesOrg": "S002",
        "DistributionChannel": "D02",
        "Division": "DV2",
        "PartnerFunctions": [
          {
            "PartnerFunction": "BP",
            "PartnerId": "P003",
            "PartnerName": "Bob Brown"
          }
        ]
      }
    ]
  }
}

JOLT转换Spec #

[
  {
    "operation": "shift",
    "spec": {
      "CustomerMaster": {
        "CustomerGeneral": {
          "*": "flatCustomers[&3].&"
        },
        "SalesAreas": {
          "*": {
            "*": "flatCustomers[&2].&",
            "PartnerFunctions": {
              "*": {
                "*": "flatCustomers[&4].&"
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "flatCustomers": {
        "*": {
          "SalesOrg": "ONE",
          "DistributionChannel": "ONE",
          "Division": "ONE",
          "CustomerId": "ONE",
          "CustomerName": "ONE",
          "Country": "ONE"
        }
      }
    }
  }
]

转换后扁平输出 #

{
  "flatCustomers": [
    {
      "CustomerId": "C001",
      "CustomerName": "ABC Corp",
      "Country": "US",
      "SalesOrg": "S001",
      "DistributionChannel": "D01",
      "Division": "DV1",
      "PartnerFunction": "SP",
      "PartnerId": "P001",
      "PartnerName": "John Doe"
    },
    {
      "CustomerId": "C001",
      "CustomerName": "ABC Corp",
      "Country": "US",
      "SalesOrg": "S001",
      "DistributionChannel": "D01",
      "Division": "DV1",
      "PartnerFunction": "SH",
      "PartnerId": "P002",
      "PartnerName": "Jane Smith"
    },
    {
      "CustomerId": "C001",
      "CustomerName": "ABC Corp",
      "Country": "US",
      "SalesOrg": "S002",
      "DistributionChannel": "D02",
      "Division": "DV2",
      "PartnerFunction": "BP",
      "PartnerId": "P003",
      "PartnerName": "Bob Brown"
    }
  ]
}

关键逻辑说明 #

• shift操作：通过索引引用（&2、&4等）将多层嵌套的字段映射到同一层级的数组对象中，每个合作伙伴函数对应一条扁平记录。
• cardinality操作：确保非重复字段（如客户基本信息、销售区域信息）在每条记录中仅保留一个值，避免数组化。

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