使用Jolt转换将扁平JSON转为带多数组的嵌套JSON并保留空值

问题说明 #

我需要编写Jolt转换规范，将扁平的JSON数组转换为包含嵌套数组的JSON结构，同时保留所有空值字段。目前的转换结果不仅丢失了空值，还无法匹配预期的输出结构。

输入JSON #

[
  {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "HType": "IN_Load",
    "loadNumber": "123345",
    "billOfLading": "12345",
    "referenceNumbers": "LID",
    "addressLine1": "ABC Street",
    "addressLine2": "null",
    "city": "Chicago",
    "country": "US",
    "latitude": "null",
    "longitude": "null",
    "earliestAppointmentTime": "XXXXX09:25",
    "latestAppointmentTime": "XXXXX09:25",
    "postalCode": "XXXXX3",
    "sequence": "1",
    "state": "XY",
    "stopReferenceId": "0001",
    "stopType": "PU",
    "truckNumber": null,
    "trailerNumber": null,
    "driverPhone": null,
    "railEquipmentInitials": null,
    "railEquipmentNumber": null,
    "containerNumber": "XXXXXXXX"
  },
  {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "HType": "IN_Load",
    "loadNumber": "123345",
    "billOfLading": "12345",
    "referenceNumbers": "LID",
    "addressLine1": "null",
    "addressLine2": "null",
    "city": "null",
    "country": "null",
    "latitude": null,
    "longitude": null,
    "earliestAppointmentTime": "XXXXX09:25",
    "latestAppointmentTime": "XXXXX09:25",
    "name": "null",
    "postalCode": "null",
    "sequence": "2",
    "state": "null",
    "stopReferenceId": "XXXXD",
    "stopType": "PL",
    "truckNumber": null,
    "trailerNumber": null,
    "driverPhone": null,
    "railEquipmentInitials": null,
    "railEquipmentNumber": null,
    "containerNumber": "XXXXXXXX"
  }
]

期望输出JSON #

{
  "load": {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "HType": [ "IN_Load" ],
    "loadNumber": "123345",
    "billOfLading": "12345",
    "referenceNumbers": [ "LID" ],
    "stops": [
      {
        "addressLine1": "ABC Street",
        "addressLine2": "null",
        "city": "Chicago",
        "country": "US",
        "earliestAppointmentTime": "XXXXX09:25",
        "latestAppointmentTime": "XXXXX09:25",
        "postalCode": "XXXXX3",
        "sequence": "1",
        "state": "XY",
        "stopReferenceId": "0001",
        "stopType": "PU"
      },
      {
        "earliestAppointmentTime": "2021-03-09T15:25:00.203Z",
        "latestAppointmentTime": "2021-03-09T15:25:00.203Z",
        "sequence": "2",
        "stopReferenceId": "dummy",
        "stopType": "PL",
        "externalAddressId": "dummy"
      }
    ]
  },
  "containerInfo": {
    "containerNumber": "XXXXXXXX"
  },
  "trackingInfo": {
    "truckNumber": null,
    "trailerNumber": null,
    "driverPhone": null,
    "railEquipmentInitials": null,
    "railEquipmentNumber": null
  }
}

当前使用的Jolt规范 #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@(1,container_id).load.stops[&1].&",
        "container_id": "@(1,container_id).load.&", // "else" case
        "shipperN": "@(1,container_id).load.&",
        "PNumber": "@(1,container_id).load.&",
        "trackingNumber": "@(1,container_id).load.&",
        "priority": "@(1,container_id).load.&",
        "HType": "@(1,container_id).load.&",
        "loadNumber": "@(1,container_id).load.&",
        "billOfLading": "@(1,container_id).load.&",
        "referenceNumbers": "@(1,container_id).load.&",
        "containerNumber": "@(1,container_id).containerInfo.&",
        "truckNumber": "@(1,container_id).trackingInfo.&",
        "trailerNumber": "@(1,container_id).trackingInfo.&",
        "driverPhone": "@(1,container_id).trackingInfo.&",
        "railEquipmentInitials": "@(1,container_id).trackingInfo.&",
        "railEquipmentNumber": "@(1,container_id).trackingInfo.&"
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "container_id": "ONE",
          "shipperN": "ONE",
          "PNumber": "ONE",
          "trackingNumber": "ONE",
          "priority": "ONE",
          "HType": "ONE",
          "referenceNumbers": "ONE",
          "loadNumber": "ONE",
          "billOfLading": "ONE",
          "containerInfo": {
            "*": "ONE"
          },
          "trackingInfo": {
            "*": "ONE"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

问题分析与修正方案 #

当前规范的核心问题：

1. 使用了recursivelySquashNulls操作，直接删除所有空值字段，与需求冲突。
2. 第一个shift操作中，*匹配所有字段导致非stop字段混入stops数组，结构混乱。
3. 未将HType和referenceNumbers转换为数组格式。

修正后的Jolt规范：

[
  // 第一步：拆分字段到对应嵌套结构，仅将stop相关字段写入stops数组
  {
    "operation": "shift",
    "spec": {
      "*": {
        // 提取stop专属字段
        "addressLine1": "@(1,container_id).load.stops[&1].&",
        "addressLine2": "@(1,container_id).load.stops[&1].&",
        "city": "@(1,container_id).load.stops[&1].&",
        "country": "@(1,container_id).load.stops[&1].&",
        "earliestAppointmentTime": "@(1,container_id).load.stops[&1].&",
        "latestAppointmentTime": "@(1,container_id).load.stops[&1].&",
        "postalCode": "@(1,container_id).load.stops[&1].&",
        "sequence": "@(1,container_id).load.stops[&1].&",
        "state": "@(1,container_id).load.stops[&1].&",
        "stopReferenceId": "@(1,container_id).load.stops[&1].&",
        "stopType": "@(1,container_id).load.stops[&1].&",
        // 顶层load字段
        "container_id": "@(1,container_id).load.&",
        "shipperN": "@(1,container_id).load.&",
        "PNumber": "@(1,container_id).load.&",
        "trackingNumber": "@(1,container_id).load.&",
        "priority": "@(1,container_id).load.&",
        "HType": "@(1,container_id).load.&",
        "loadNumber": "@(1,container_id).load.&",
        "billOfLading": "@(1,container_id).load.&",
        "referenceNumbers": "@(1,container_id).load.&",
        // containerInfo字段
        "containerNumber": "@(1,container_id).containerInfo.&",
        // trackingInfo字段
        "truckNumber": "@(1,container_id).trackingInfo.&",
        "trailerNumber": "@(1,container_id).trackingInfo.&",
        "driverPhone": "@(1,container_id).trackingInfo.&",
        "railEquipmentInitials": "@(1,container_id).trackingInfo.&",
        "railEquipmentNumber": "@(1,container_id).trackingInfo.&"
      }
    }
  },
  // 第二步：将HType和referenceNumbers转为数组，保留空值
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "load": {
          "HType": ["@(1,HType)"],
          "referenceNumbers": ["@(1,referenceNumbers)"]
        }
      }
    }
  },
  // 第三步：去重，确保同container_id的字段只保留一个实例
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "load": {
          "container_id": "ONE",
          "shipperN": "ONE",
          "PNumber": "ONE",
          "trackingNumber": "ONE",
          "priority": "ONE",
          "HType": "ONE",
          "referenceNumbers": "ONE",
          "loadNumber": "ONE",
          "billOfLading": "ONE"
        },
        "containerInfo": {
          "*": "ONE"
        },
        "trackingInfo": {
          "*": "ONE"
        }
      }
    }
  },
  // 第四步：移除外层的container_id键，调整为最终结构
  {
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

说明 #

• 移除recursivelySquashNulls操作，确保空值字段被保留。
• 明确指定stop相关字段，避免非stop字段混入stops数组。
• 通过modify-overwrite-beta将HType和referenceNumbers包装为数组。
• 调整cardinality的路径，确保精准去重。

注：期望输出中第二个stop的earliestAppointmentTime、latestAppointmentTime、stopReferenceId和externalAddressId为示例值，若需要从输入转换需额外添加映射逻辑，当前规范基于输入字段生成对应结构。

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