使用JOLT去除转义斜杠并提取嵌套datatag JSON的技术求助

可以通过两步JOLT操作完成转义斜杠去除与datatag数据提取，最终输出适合NiFi加载的结构化JSON数组：

JOLT规范 #

[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        // 提取datatag对应的JSON片段
        "temp": "=substringAfter(@(1,json), '\"datatag\":')",
        "temp": "=substringBefore(@(1,temp), ',\"endtag\"')",
        // 将字符串解析为JSON对象，自动去除转义斜杠
        "datatag": "=jsonParse(@(1,temp))"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "datatag": "[&1]"
      }
    }
  }
]

操作说明 #

1. 第一步（modify-overwrite-beta）：

   • 遍历输入数组的每个元素（用*匹配）
   • 通过substringAfter截取"datatag":之后的内容，再用substringBefore去掉末尾的",\"endtag\""，得到纯净的datatag JSON字符串
   • 调用jsonParse函数将字符串解析为JSON对象，转义斜杠会被自动处理

2. 第二步（shift）：

   • 提取每个元素中解析后的datatag对象，重新组织成数组结构，输出结果可直接用于NiFi加载到数据表

输出示例 #

处理后会得到如下结构化数组：

[
  {
    "Item": "000000000010000057",
    "Location": "NL01",
    "ERPGoodsReceiptTimeIL": "0",
    "ERPLotSizeIncrementIL": "0",
    "ERPMinLotsizeIL": "0.000",
    "ERPPlannedDeliveryTimeIL": "1",
    "ERPProcurementTypeInput": "F",
    "U_SPL_PROC_TYPE": "",
    "U_ERPReOrderPointQuantity": "0",
    "U_ERPTargetInventory": "0.000",
    "U_ERPDOSTarget": "00",
    "U_Purchasing_Group": "",
    "U_LGPRO": "9300",
    "U_LGFSB": "9300",
    "OperationType": "INSERT",
    "TimeStamp": "Mar 13 2024 3:00PM"
  },
  {
    "Item": "000000000010015005",
    "Location": "NL01",
    "ERPGoodsReceiptTimeIL": "10",
    "ERPLotSizeIncrementIL": "1",
    "ERPMinLotsizeIL": "8.000",
    "ERPPlannedDeliveryTimeIL": "5",
    "ERPProcurementTypeInput": "E",
    "U_SPL_PROC_TYPE": "",
    "U_ERPReOrderPointQuantity": "0",
    "U_ERPTargetInventory": "0.000",
    "U_ERPDOSTarget": "00",
    "U_Purchasing_Group": "101",
    "U_LGPRO": "1000",
    "U_LGFSB": "2000",
    "OperationType": "INSERT",
    "TimeStamp": "Mar 13 2024 3:00PM"
  },
  {
    "Item": "000000000010000108",
    "Location": "NL01",
    "ERPGoodsReceiptTimeIL": "0",
    "ERPLotSizeIncrementIL": "0",
    "ERPMinLotsizeIL": "0.000",
    "ERPPlannedDeliveryTimeIL": "1",
    "ERPProcurementTypeInput": "F",
    "U_SPL_PROC_TYPE": "",
    "U_ERPReOrderPointQuantity": "0",
    "U_ERPTargetInventory": "0.000",
    "U_ERPDOSTarget": "00",
    "U_Purchasing_Group": "",
    "U_LGPRO": "9300",
    "U_LGFSB": "9300",
    "OperationType": "INSERT",
    "TimeStamp": "Mar 13 2024 3:00PM"
  }
]

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