Jolt transformation to grouping ID by inside the nested json

VishalK
August 30, 2024
88 views
0 votes
2 Answers

I have below tried to build the json by using the jolt spec by remove the id in the nested json array please help me to provide the expected out json below Transactions object grouping ID needs to be corrected by removing the id
As shown below in expected output json

**Input 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 spec tried
**

[
  {
    "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[]"
        }
      }
    }
  }]

Expected output

[ {
  "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"
      } ]
    }
  }
} ]

actual output

[ {
  "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"
      } ]
    }
  }
} ]

Answers

I think you are on track in the first two transformation spec, however in the 3rd transformation you seem to want to re bucket each group level into an array vs having group header field and I dont think this is possible in one transformation. Each group level has to be done using its own shift transformation starting from the highest group level then going downward since the next group level will depend on how the parent group level is structured if that makes any sense.

With that after the cardinality transformation you would have the following 3 shift transformation for each group level (workitem -> wfi_reasons -> transactions):

[...
 {
    "operation": "shift",
    "spec": {
      "*": "[].WF_Workitem"
    }
  }
  ,
  {
    "operation": "shift",
    "spec": {
      "*": {
        "WF_Workitem": {
          "*": "[&2].&1.&",
          "WFI_Reasons": {
            "*": "[&3].&2.WF_Instance.&1[]"
          }
        }
      }
    }
  }
  ,
  {
    "operation": "shift",
    "spec": {
      "*": {
        "WF_Workitem": {
          "*": "[&2].&1.&",
          "WF_Instance": {
            "WFI_Reasons": {
              "*": {
                "*": "[&5].&4.&3.&2[&1].&",
                "Transactions": {
                  "*": "[&6].&5.&4.&3[&2].&1[]"
                }
              }
            }
          }
        }
      }
    }
  }]

Also I would recommend in these scenarios looking into jslt which is another transformation language for json (if you are using Nifi, there is processor for that as well). Given that jslt has group-by function, the transformation is going to be easier and more readable with much less lines of codes as in the following:

import "http://jslt.schibsted.com/2018/experimental" as exp

let id_group=  exp:group-by(., {"id":.ALERT_IDENTIFIER,"CreationTime":.CREATION_TIMESTAMP},.)

let id_nested_group= [ for($id_group) 
                          {
                             "WF_Workitem":{
                                "WF_Instance" :{
                                     let x= exp:group-by(.values,
                                                        {"SRC_AccountID":.DATASOURCE_ACC_ID,
                                                         "SRC_ID":.REASON_SRC_UNIQUE_ID,
                                                          "DetectionScore":.REASON_SCORE,
                                                          "ReasonTimePeriod":.REASON_TIME_PERIOD,
                                                          "ReasonDescription":.REASON_DESCRIPTION},
                                                        {"AccountID":.ACCOUNT_ID,
                                                          "TransactionID":.TRANSACTION_ID
                                                        })
                                     "WFI_Reasons": [for($x) {"Transactions":.values}+.key]
                                              }
                                         }+.key
                         }
                   ]

$id_nested_group

Hope you find this helpful.

Similar to @samer-saleh’s recommendation, you may want to try another JSON transformation language Josson that is for Java. It also has group-by function and the transformation expression for your case is much shorter.

https://github.com/octomix/josson

Josson josson = Josson.fromJsonString(inputJson);
JsonNode node = josson.getNode(
    "group(" +
    "  key: map(" +
    "    id: ALERT_IDENTIFIER," +
    "    CreationTime: CREATION_TIMESTAMP)" +
    ")@" +
    ".map(" +
    "  WF_Workitem: key.field(" +
    "    WF_Instance: map(" +
    "      WFI_Reasons: ..elements.group(" +
    "        key: map(" +
    "          SRC_AccountID: DATASOURCE_ACC_ID," +
    "          SRC_ID: REASON_SRC_UNIQUE_ID," +
    "          DetectionScore: REASON_SCORE," +
    "          ReasonTimePeriod: REASON_TIME_PERIOD," +
    "          ReasonDescription: REASON_DESCRIPTION)," +
    "        map(" +
    "          AccountID: ACCOUNT_ID," +
    "          TransactionID: TRANSACTION_ID)" +
    "      )@" +
    "      .key.field(Transactions: ..elements)" +
    "    )" +
    "  )" +
    ")");
System.out.println(node.toPrettyString());

Output

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

Please signup or login to give your own answer.

Click here to cancel reply.