Jolt转换优化：移除嵌套JSON中Transactions的ID分组

我尝试用Jolt转换JSON数据，需要移除嵌套JSON数组中的ID，同时修正Transactions的分组方式——把当前以TransactionID为键的结构改成数组形式，但实际输出未达到预期，求正确的Jolt配置生成目标输出。

输入JSON #

[
  {
    "ALERT_IDENTIFIER": "123",
    "ACCOUNT_ID": "Acc345",
    "CREATION_TIMESTAMP": 1615902235602,
    "TRANSACTION_ID": "Tr123",
    "REASON_SRC_UNIQUE_ID": "TEST123",
    "REASON_SCORE": 67,
    "REASON_TIME_PERIOD": "Daily",
    "REASON_DESCRIPTION": "CB05b: In the previous day there was a high amount",
    "DATASOURCE_ACC_ID": "NOCTEST1234"
  },
  {
    "ALERT_IDENTIFIER": "123",
    "ACCOUNT_ID": "Acc3478",
    "CREATION_TIMESTAMP": 1615902235602,
    "TRANSACTION_ID": "Tr12356",
    "REASON_SRC_UNIQUE_ID": "TEST123456",
    "REASON_SCORE": 87,
    "REASON_TIME_PERIOD": "Daily",
    "REASON_DESCRIPTION": "CB05b: In the previous day there was a high amount",
    "DATASOURCE_ACC_ID": "NOCTEST1234"
  },
  {
    "ALERT_IDENTIFIER": "123",
    "ACCOUNT_ID": "Acc3456",
    "CREATION_TIMESTAMP": 1615902235602,
    "TRANSACTION_ID": "Tr123678",
    "REASON_SRC_UNIQUE_ID": "TEST123456",
    "REASON_SCORE": 87,
    "REASON_TIME_PERIOD": "Daily",
    "REASON_DESCRIPTION": "CB05b: In the previous day there was a high amount",
    "DATASOURCE_ACC_ID": "NOCTEST1234"
  }
]

尝试过的Jolt配置 #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "ALERT_IDENTIFIER": "@1,ALERT_IDENTIFIER.id",
        "CREATION_TIMESTAMP": "@1,ALERT_IDENTIFIER.CreationTime",
        "DATASOURCE_ACC_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.SRC_AccountID",
        "REASON_SRC_UNIQUE_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.SRC_ID",
        "REASON_SCORE": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.DetectionScore",
        "REASON_TIME_PERIOD": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.ReasonTimePeriod",
        "ACCOUNT_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.Transactions.@1,TRANSACTION_ID.AccountID",
        "TRANSACTION_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.Transactions.@1,TRANSACTION_ID.TransactionID",
        "REASON_DESCRIPTION": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.ReasonDescription"
      }
    }
  },
  { //get rid of repetitions, eg. convert arrays to attributes 
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE",
        "WFI_Reasons": {
          "*": {
            "*": "ONE"
          }
        }
      }
    }
  }, {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[#2].WF_Workitem.&",
        "WFI_Reasons": {
          "*": "[#3].WF_Workitem.WF_Instance.&1[]"
        }
      }
    }
  }]

预期输出 #

[ {
  "WF_Workitem" : {
    "id" : "123",
    "CreationTime" : 1615902235602,
    "WF_Instance" : {
      "WFI_Reasons" : [ {
        "SRC_AccountID" : "NOCTEST1234",
        "SRC_ID" : "TEST123",
        "DetectionScore" : 67,
        "ReasonTimePeriod" : "Daily",
        "Transactions" : [{
            
            "AccountID" : "Acc345",
            "TransactionID" : "Tr123"
          
        }],
        "ReasonDescription" : "CB05b: In the previous day there was a high amount"
      }, {
        "SRC_AccountID" : "NOCTEST1234",
        "SRC_ID" : "TEST123456",
        "DetectionScore" : 87,
        "ReasonTimePeriod" : "Daily",
        "Transactions" : [
          {
            "AccountID" : "Acc3478",
            "TransactionID" : "Tr12356"
          },
           {
            "AccountID" : "Acc3456",
            "TransactionID" : "Tr123678"
          }
        ],
        "ReasonDescription" : "CB05b: In the previous day there was a high amount"
      } ]
    }
  }
} ]

实际输出 #

[ {
  "WF_Workitem" : {
    "id" : "123",
    "CreationTime" : 1615902235602,
    "WF_Instance" : {
      "WFI_Reasons" : [ {
        "SRC_AccountID" : "NOCTEST1234",
        "SRC_ID" : "TEST123",
        "DetectionScore" : 67,
        "ReasonTimePeriod" : "Daily",
        "Transactions" : {
          "Tr123" : {
            "AccountID" : "Acc345",
            "TransactionID" : "Tr123"
          }
        },
        "ReasonDescription" : "CB05b: In the previous day there was a high amount"
      }, {
        "SRC_AccountID" : "NOCTEST1234",
        "SRC_ID" : "TEST123456",
        "DetectionScore" : 87,
        "ReasonTimePeriod" : "Daily",
        "Transactions" : {
          "Tr12356" : {
            "AccountID" : "Acc3478",
            "TransactionID" : "Tr12356"
          },
          "Tr123678" : {
            "AccountID" : "Acc3456",
            "TransactionID" : "Tr123678"
          }
        },
        "ReasonDescription" : "CB05b: In the previous day there was a high amount"
      } ]
    }
  }
} ]

修正后的Jolt配置 #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "ALERT_IDENTIFIER": "@1,ALERT_IDENTIFIER.id",
        "CREATION_TIMESTAMP": "@1,ALERT_IDENTIFIER.CreationTime",
        "DATASOURCE_ACC_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.SRC_AccountID",
        "REASON_SRC_UNIQUE_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.SRC_ID",
        "REASON_SCORE": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.DetectionScore",
        "REASON_TIME_PERIOD": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.ReasonTimePeriod",
        "REASON_DESCRIPTION": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.ReasonDescription",
        // 修改Transactions部分，用数组索引而非TransactionID作为键
        "ACCOUNT_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.Transactions[#2].AccountID",
        "TRANSACTION_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.Transactions[#2].TransactionID"
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE",
        "WFI_Reasons": {
          "*": {
            "*": "ONE",
            // 确保Transactions是数组，保留所有元素
            "Transactions": "MANY"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[#2].WF_Workitem.&",
        "WFI_Reasons": {
          "*": "[#3].WF_Workitem.WF_Instance.&1[]"
        }
      }
    }
  }
]

关键修改说明 #

1. Transactions数组化：在第一个shift操作中，把原来的Transactions.@1,TRANSACTION_ID.xxx改成Transactions[#2].xxx，#2表示当前层级的索引，这样每个交易数据会被作为数组的独立元素，而非以TransactionID为键的对象。
2. Cardinality调整：在cardinality操作里给Transactions指定MANY，确保同一Reason下的多个交易都被保留在数组中，不会被合并为单个对象。

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