Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JOLT spec for grouping by ID and removing the duplicates entry inside the couple of array in nested json

ManjunathK
August 28, 2024
47 views
0 votes
2 Answers

Please help me in providing jolt spec or update the below tried jolt spec to get expected output json shown below, i have added few default value as part of requirement and not able to remove the duplicate entry inside the WFI_Customer and WFI_Reasons arrays, please help in fixing the issue thanks in advance.

input json

[
  {
    "ALERT_IDENTIFIER": "123",
    "FIRST_REASON": "CB05b High amount credit transactions compared to credit history",
    "ALERT_TEXT": "CB05b: In the previous day there was a high amount",
    "ALERT_SCORE": 27,
    "EVENT_DATE": 1615660200000,
    "CUSTOMER_ID": "CUST12",
    "CUSTOMER_SEGMENT": "SILVER",
    "CUSTOMER_TYPE_CODE": "P",
    "QUEUE_FASTTRACK_FLAG": "0",
    "CHECK_NAME": null,
    "ACCOUNT_ID": "-1",
    "TRANSACTION_ID": null,
    "WW_ORGUNIT_ID": 1045,
    "WW_ORGUNIT_CODE": "SE_EMP",
    "CREATION_TIMESTAMP": 1615902235602,
    "REASON_SRC_UNIQUE_ID": "TEST123456",
    "REASON_NAME": "CB05b High amount credit transactions compared to credit history",
    "REASON_SCORE": 27,
    "REASON_TIME_PERIOD": "Daily",
    "REASON_DESCRIPTION": "CB05b: In the previous day there was a high amount",
    "DATASOURCE_RECORD_ID": null,
    "DATASOURCE_ACC_ID": "NOCTEST1234",
    "RISK_LEVEL": "Low",
    "EVENT_TIMESTAMP": 1615660200000,
    "LINKED_ENTITY_NAME": "Test2",
    "LINKED_ENTITY_KEY": "2"
  },
  {
    "ALERT_IDENTIFIER": "123",
    "FIRST_REASON": "CB05b High amount credit transactions compared to credit history",
    "ALERT_TEXT": "CB05b: In the previous day there was a high amount",
    "ALERT_SCORE": 27,
    "EVENT_DATE": 1615660200000,
    "CUSTOMER_ID": "CUST12",
    "CUSTOMER_SEGMENT": "SILVER",
    "CUSTOMER_TYPE_CODE": "P",
    "QUEUE_FASTTRACK_FLAG": "0",
    "CHECK_NAME": null,
    "ACCOUNT_ID": "-1",
    "TRANSACTION_ID": null,
    "WW_ORGUNIT_ID": 1045,
    "WW_ORGUNIT_CODE": "SE_EMP",
    "CREATION_TIMESTAMP": 1615902235602,
    "REASON_SRC_UNIQUE_ID": "TEST123456",
    "REASON_NAME": "CB05b High amount credit transactions compared to credit history",
    "REASON_SCORE": 27,
    "REASON_TIME_PERIOD": "Daily",
    "REASON_DESCRIPTION": "CB05b: In the previous day there was a high amount",
    "DATASOURCE_RECORD_ID": null,
    "DATASOURCE_ACC_ID": "NOCTEST1234",
    "RISK_LEVEL": "Low",
    "EVENT_TIMESTAMP": 1615660200000,
    "LINKED_ENTITY_NAME": null,
    "LINKED_ENTITY_KEY": null
  },
  {
    "ALERT_IDENTIFIER": "123",
    "FIRST_REASON": "CB05b High amount credit transactions compared to credit history",
    "ALERT_TEXT": "CB05b: In the previous day there was a high amount",
    "ALERT_SCORE": 27,
    "EVENT_DATE": 1615660200000,
    "CUSTOMER_ID": "CUST12345",
    "CUSTOMER_SEGMENT": "GOLD",
    "CUSTOMER_TYPE_CODE": "P",
    "QUEUE_FASTTRACK_FLAG": "0",
    "CHECK_NAME": null,
    "ACCOUNT_ID": "-1",
    "TRANSACTION_ID": null,
    "WW_ORGUNIT_ID": 1045,
    "WW_ORGUNIT_CODE": "SE_EMP",
    "CREATION_TIMESTAMP": 1615902235602,
    "REASON_SRC_UNIQUE_ID": "TEST123",
    "REASON_NAME": "CB05b High amount credit transactions compared to credit history",
    "REASON_SCORE": 47,
    "REASON_TIME_PERIOD": "Daily",
    "REASON_DESCRIPTION": "CB05b: In the previous day there was a high amount",
    "DATASOURCE_RECORD_ID": null,
    "DATASOURCE_ACC_ID": "NOCTEST456",
    "RISK_LEVEL": "High",
    "EVENT_TIMESTAMP": 1615660200000,
    "LINKED_ENTITY_NAME": "Test1",
    "LINKED_ENTITY_KEY": "1"
  }
]

actual output

[ {
  "WF_Workitem" : {
    "GlobalID" : "AMALERT123",
    "InvLifecycleStatus" : null,
    "SourceSystem" : "AM",
    "WF_Instance" : {
      "Description" : "CB05b: In the previous day there was a high amount",
      "EventDate" : 1615660200000,
      "External_Data_Reference" : [ {
        "ApplicationId" : "null",
        "External_Data_Reference_Type_Dict" : [ {
          "Description" : "Account Type - ID stored is the Account_ID",
          "Name" : "Account_ID",
          "ReferenceTypeId" : "1"
        } ],
        "InstanceID" : "123",
        "ReferenceID" : "-1",
        "ReferenceTypeId" : "1"
      }, {
        "ApplicationId" : "null",
        "External_Data_Reference_Type_Dict" : [ {
          "Description" : "Transaction Type - ID stored is the Transaction_ID",
          "Name" : "Transaction_ID",
          "ReferenceTypeId" : "2"
        } ],
        "InstanceID" : "123",
        "ReferenceID" : null,
        "ReferenceTypeId" : "2"
      } ],
      "FastTrack" : "NO",
      "FirstReasonName" : "CB05b High amount credit transactions compared to credit history",
      "InstanceID" : "123",
      "OrgUnitCode" : "SE_EMP",
      "OrgUnitID" : 1045,
      "Score" : 27,
      "SubScenarioName" : null,
      "WFI_Customer" : [ {
        "CustomerID" : "CUST12",
        "CustomerSegment" : "SILVER",
        "CustomerTypeCode" : "NATURAL PERSON"
      }, {
        "CustomerID" : "CUST12",
        "CustomerSegment" : "SILVER",
        "CustomerTypeCode" : "NATURAL PERSON"
      }, {
        "CustomerID" : "CUST12345",
        "CustomerSegment" : "GOLD",
        "CustomerTypeCode" : "NATURAL PERSON"
      } ],
      "WFI_Reasons" : [ {
        "CustomerRiskLevel" : "LOW",
        "DetectionScore" : 27,
        "DetectionTimeStamp" : 1615660200000,
        "ReasonDescription" : "CB05b: In the previous day there was a high amount",
        "ReasonTimePeriod" : "Daily",
        "SRC_AccountID" : "NOCTEST1234",
        "SRC_ID" : "TEST123456",
        "SRC_TransactionID" : null,
        "ScenarioName" : "CB05b High amount credit transactions compared to credit history"
      }, {
        "CustomerRiskLevel" : "LOW",
        "DetectionScore" : 27,
        "DetectionTimeStamp" : 1615660200000,
        "ReasonDescription" : "CB05b: In the previous day there was a high amount",
        "ReasonTimePeriod" : "Daily",
        "SRC_AccountID" : "NOCTEST1234",
        "SRC_ID" : "TEST123456",
        "SRC_TransactionID" : null,
        "ScenarioName" : "CB05b High amount credit transactions compared to credit history"
      }, {
        "CustomerRiskLevel" : "HIGH",
        "DetectionScore" : 47,
        "DetectionTimeStamp" : 1615660200000,
        "ReasonDescription" : "CB05b: In the previous day there was a high amount",
        "ReasonTimePeriod" : "Daily",
        "SRC_AccountID" : "NOCTEST456",
        "SRC_ID" : "TEST123",
        "SRC_TransactionID" : null,
        "ScenarioName" : "CB05b High amount credit transactions compared to credit history"
      } ],
      "WFinstanceType" : "ALERT"
    },
    "WF_Linked_References" : {
      "Global_ID" : null,
      "Local_ID" : null,
      "RelationshipType" : null,
      "SourceRef" : null
    },
    "WF_Workitem_Link" : [ {
      "LinkedEntityKey" : "2",
      "LinkedEntityName" : "Test2"
    }, {
      "LinkedEntityKey" : null,
      "LinkedEntityName" : null
    }, {
      "LinkedEntityKey" : "1",
      "LinkedEntityName" : "Test1"
    } ],
    "WFeventTimestamp" : 1615902235602,
    "opDomain" : "TM"
  }
} ]

Expected output

[ {
  "WF_Workitem" : {
    "GlobalID" : "AMALERT123",
    "InvLifecycleStatus" : null,
    "SourceSystem" : "AM",
    "WF_Instance" : {
      "Description" : "CB05b: In the previous day there was a high amount",
      "EventDate" : 1615660200000,
      "External_Data_Reference" : [ {
        "ApplicationId" : "null",
        "External_Data_Reference_Type_Dict" : [ {
          "Description" : "Account Type - ID stored is the Account_ID",
          "Name" : "Account_ID",
          "ReferenceTypeId" : "1"
        } ],
        "InstanceID" : "123",
        "ReferenceID" : "-1",
        "ReferenceTypeId" : "1"
      }, {
        "ApplicationId" : "null",
        "External_Data_Reference_Type_Dict" : [ {
          "Description" : "Transaction Type - ID stored is the Transaction_ID",
          "Name" : "Transaction_ID",
          "ReferenceTypeId" : "2"
        } ],
        "InstanceID" : "123",
        "ReferenceID" : null,
        "ReferenceTypeId" : "2"
      } ],
      "FastTrack" : "NO",
      "FirstReasonName" : "CB05b High amount credit transactions compared to credit history",
      "InstanceID" : "123",
      "OrgUnitCode" : "SE_EMP",
      "OrgUnitID" : 1045,
      "Score" : 27,
      "SubScenarioName" : null,
      "WFI_Customer" : [ {
        "CustomerID" : "CUST12",
        "CustomerSegment" : "SILVER",
        "CustomerTypeCode" : "NATURAL PERSON"
      }, {
        "CustomerID" : "CUST12345",
        "CustomerSegment" : "GOLD",
        "CustomerTypeCode" : "NATURAL PERSON"
      } ],
      "WFI_Reasons" : [ {
        "CustomerRiskLevel" : "LOW",
        "DetectionScore" : 27,
        "DetectionTimeStamp" : 1615660200000,
        "ReasonDescription" : "CB05b: In the previous day there was a high amount",
        "ReasonTimePeriod" : "Daily",
        "SRC_AccountID" : "NOCTEST1234",
        "SRC_ID" : "TEST123456",
        "SRC_TransactionID" : null,
        "ScenarioName" : "CB05b High amount credit transactions compared to credit history"
      }, {
        "CustomerRiskLevel" : "HIGH",
        "DetectionScore" : 47,
        "DetectionTimeStamp" : 1615660200000,
        "ReasonDescription" : "CB05b: In the previous day there was a high amount",
        "ReasonTimePeriod" : "Daily",
        "SRC_AccountID" : "NOCTEST456",
        "SRC_ID" : "TEST123",
        "SRC_TransactionID" : null,
        "ScenarioName" : "CB05b High amount credit transactions compared to credit history"
      } ],
      "WFinstanceType" : "ALERT"
    },
    "WF_Linked_References" : {
      "Global_ID" : null,
      "Local_ID" : null,
      "RelationshipType" : null,
      "SourceRef" : null
    },
    "WF_Workitem_Link" : [ {
      "LinkedEntityKey" : "2",
      "LinkedEntityName" : "Test2"
    }, {
      "LinkedEntityKey" : null,
      "LinkedEntityName" : null
    }, {
      "LinkedEntityKey" : "1",
      "LinkedEntityName" : "Test1"
    } ],
    "WFeventTimestamp" : 1615902235602,
    "opDomain" : "TM"
  }
} ]

jolt spec tried

[
  {
    "operation": "modify-default-beta",
    "spec": {
      "*": {
        "comp_id": "=concat(@(1,ALERT_IDENTIFIER),'_', @(1,FIRST_REASON))"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@(1,comp_id).&[]"
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "CREATION_TIMESTAMP": "=firstElement(@(1,CREATION_TIMESTAMP))",
        "ALERT_IDENTIFIER": "=firstElement(@(1,ALERT_IDENTIFIER))",
        "FIRST_REASON": "=firstElement(@(1,FIRST_REASON))",
        "ALERT_TEXT": "=firstElement(@(1,ALERT_TEXT))",
        "ALERT_SCORE": "=firstElement(@(1,ALERT_SCORE))",
        "EVENT_DATE": "=firstElement(@(1,EVENT_DATE))",
        "WW_ORGUNIT_ID": "=firstElement(@(1,WW_ORGUNIT_ID))",
        "WW_ORGUNIT_CODE": "=firstElement(@(1,WW_ORGUNIT_CODE))",
        "WW_DOMAIN_CODE": "=firstElement(@(1,WW_DOMAIN_CODE))",
        "CHECK_NAME": "=firstElement(@(1,CHECK_NAME))",
        "ACCOUNT_ID": "=firstElement(@(1,ACCOUNT_ID))",
        "TRANSACTION_ID": "=firstElement(@(1,TRANSACTION_ID))",
        "QUEUE_FASTTRACK_FLAG": "=firstElement(@(1,QUEUE_FASTTRACK_FLAG))"
      }
    }
  },

  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[#2].&",
        "comp_id": null
      }
    }
  }
,
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[#2].&",
        "CREATION_TIMESTAMP": "[&1].WF_Workitem.WFeventTimestamp",
        "ALERT_IDENTIFIER": ["[&1].WF_Workitem.WF_Instance.InstanceID", "[&1].WF_Workitem.WF_Instance.External_Data_Reference[0].InstanceID", "[&1].WF_Workitem.WF_Instance.External_Data_Reference[1].InstanceID"],
        "FIRST_REASON": "[&1].WF_Workitem.WF_Instance.FirstReasonName",
        "ALERT_TEXT": "[&1].WF_Workitem.WF_Instance.Description",
        "ALERT_SCORE": "[&1].WF_Workitem.WF_Instance.Score",
        "EVENT_DATE": "[&1].WF_Workitem.WF_Instance.EventDate",
        "WW_ORGUNIT_ID": "[&1].WF_Workitem.WF_Instance.OrgUnitID",
        "WW_ORGUNIT_CODE": "[&1].WF_Workitem.WF_Instance.OrgUnitCode",
        "WW_DOMAIN_CODE": "[&1].WF_Workitem.WF_Instance.WFinstanceSubtype",
        "ACCOUNT_ID": "[&1].WF_Workitem.WF_Instance.External_Data_Reference[0].ReferenceID",
        "TRANSACTION_ID": "[&1].WF_Workitem.WF_Instance.External_Data_Reference[1].ReferenceID",
        "CHECK_NAME": "[&1].WF_Workitem.WF_Instance.SubScenarioName",
        "#null": ["[&1].WF_Workitem.WF_Instance.External_Data_Reference[0].ApplicationId", "[&1].WF_Workitem.WF_Instance.External_Data_Reference[1].ApplicationId"],
        "#1": ["[&1].WF_Workitem.WF_Instance.External_Data_Reference[0].External_Data_Reference_Type_Dict[0].ReferenceTypeId", "[&1].WF_Workitem.WF_Instance.External_Data_Reference[0].ReferenceTypeId"],
        "#2": ["[&1].WF_Workitem.WF_Instance.External_Data_Reference[1].External_Data_Reference_Type_Dict[0].ReferenceTypeId", "[&1].WF_Workitem.WF_Instance.External_Data_Reference[1].ReferenceTypeId"],
        "#Account_ID": "[&1].WF_Workitem.WF_Instance.External_Data_Reference[0].External_Data_Reference_Type_Dict[0].Name",
        "#Transaction_ID": "[&1].WF_Workitem.WF_Instance.External_Data_Reference[1].External_Data_Reference_Type_Dict[0].Name",
        "#Account Type - ID stored is the Account_ID": "[&1].WF_Workitem.WF_Instance.External_Data_Reference[0].External_Data_Reference_Type_Dict[0].Description",
        "#Transaction Type - ID stored is the Transaction_ID": "[&1].WF_Workitem.WF_Instance.External_Data_Reference[1].External_Data_Reference_Type_Dict[0].Description",
        "#TM": "[&1].WF_Workitem.opDomain",
        "#AM": "[&1].WF_Workitem.SourceSystem",
        "#ALERT": "[&1].WF_Workitem.WF_Instance.WFinstanceType",
        "CUSTOMER_TYPE_CODE": {
          "*": {
            "P": {
              "#NATURAL PERSON": "[&4].CUSTOMER_TYPE_CODE[]"
            },
            "C": {
              "#ORGANIZATION": "[&4].CUSTOMER_TYPE_CODE[]"
            }
          }
        },
        "CUSTOMER_ID": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Customer[&].CustomerID"
        },
        "CUSTOMER_SEGMENT": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Customer[&].CustomerSegment"
        },
        "REASON_SRC_UNIQUE_ID": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].SRC_ID"
        },
        "REASON_NAME": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].ScenarioName"
        },
        "REASON_SCORE": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].DetectionScore"
        },
        "REASON_TIME_PERIOD": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].ReasonTimePeriod"
        },
        "REASON_DESCRIPTION": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].ReasonDescription"
        },
        "DATASOURCE_ACC_ID": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].SRC_AccountID"
        },
        "DATASOURCE_RECORD_ID": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].SRC_TransactionID"
        },
        "RISK_LEVEL": {
          "*": {
            "Low": {
              "#LOW": "[&4].RISK_LEVEL[]"
            },
            "Medium": {
              "#MEDIUM": "[&4].RISK_LEVEL[]"
            },
            "High": {
              "#HIGH": "[&4].RISK_LEVEL[]"
            }
          }
        },
        "EVENT_TIMESTAMP": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].DetectionTimeStamp"
        },
        "LINKED_ENTITY_KEY": {
          "*": "[&2].WF_Workitem.WF_Workitem_Link[&].LinkedEntityKey"
        },
        "LINKED_ENTITY_NAME": {
          "*": "[&2].WF_Workitem.WF_Workitem_Link[&].LinkedEntityName"
        }
      }
    }
  },
  {
    "operation": "default",
    "spec": {
      "*": {
        "QUEUE_FASTTRACK_FLAG": "NO",
        "WF_Workitem": {
          "InvLifecycleStatus": null,
          "WF_Linked_References": {
            "Global_ID": null,
            "Local_ID": null,
            "SourceRef": null,
            "RelationshipType": null
          },
          "WF_Instance": {
            "WFI_Customer[]": {
              "*": {
                "CustomerTypeCode": null
              }
            },
            "WFI_Reasons[]": {
              "*": {
                "CustomerRiskLevel": null
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "remove",
    "spec": {
      "*": {
        "ALERT_KEY": ""
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "WW_ORGUNIT_ID": "=toString",
        "WF_Workitem": {
          "GlobalID": "=concat(@(1,SourceSystem),'',@(1,opDoamin),'',@(1,WF_Instance.WFinstanceType),'',@(1,WF_Instance.InstanceID))"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[#2].&",
        "QUEUE_FASTTRACK_FLAG": {
          "NO": {
            "#NO": "[&3].WF_Workitem.WF_Instance.FastTrack"
          },
          "1": {
            "#YES": "[&3].WF_Workitem.WF_Instance.FastTrack"
          },
          "0": {
            "#NO": "[&3].WF_Workitem.WF_Instance.FastTrack"
          }
        },
        "CUSTOMER_TYPE_CODE": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Customer[&].CustomerTypeCode"
        },
        "RISK_LEVEL": {
          "*": "[&2].WF_Workitem.WF_Instance.WFI_Reasons[&].CustomerRiskLevel"
        }
      }
    }
  },
  {
    "operation": "sort"
}
]

Answers

Chosen as BEST ANSWER

i have tried below Jolt spec and it is working fine for solution i am looking for

spec

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "ALERT_IDENTIFIER": "@1,ALERT_IDENTIFIER.InstanceID",
        "FIRST_REASON": "@1,ALERT_IDENTIFIER.FirstReasonName",
        "ALERT_SCORE": "@1,ALERT_IDENTIFIER.Score",
        "ALERT_TEXT": "@1,ALERT_IDENTIFIER.Description",
        "CREATION_TIMESTAMP": "@1,ALERT_IDENTIFIER.WFeventTimestamp",
        "DATASOURCE_RECORD_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.SRC_TransactionID",
        "DATASOURCE_ACC_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.SRC_AccountID",
        "EVENT_TIMESTAMP": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.DetectionTimeStamp",
        "REASON_SRC_UNIQUE_ID": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.SRC_ID",
        "REASON_NAME": "@1,ALERT_IDENTIFIER.WFI_Reasons.@1,REASON_SRC_UNIQUE_ID.ScenarioName",
        "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",
        "LINKED_ENTITY_NAME": "@1,ALERT_IDENTIFIER.WF_Workitem_Link.@1,LINKED_ENTITY_KEY.LinkedEntityName",
        "LINKED_ENTITY_KEY": "@1,ALERT_IDENTIFIER.WF_Workitem_Link.@1,LINKED_ENTITY_KEY.LinkedEntityKey",
        "CUSTOMER_ID": "@1,ALERT_IDENTIFIER.WFI_Customer.@1,CUSTOMER_ID.CustomerID",
        "CUSTOMER_SEGMENT": "@1,ALERT_IDENTIFIER.WFI_Customer.@1,CUSTOMER_ID.CustomerSegment",
        "CUSTOMER_TYPE_CODE": {
          "P": {
            "#Natual Person": "@3,ALERT_IDENTIFIER.WFI_Customer.@3,CUSTOMER_ID.CustomerTypeCode"
          },
          "O": {
            "#Organization": "@3,ALERT_IDENTIFIER.WFI_Customer.@3,CUSTOMER_ID.CustomerTypeCode"
          }
        },
        "QUEUE_FASTTRACK_FLAG": {
          "0": {
            "#NO": "@3,ALERT_IDENTIFIER.FastTrack"
          },
          "*": {
            "#YES": "@3,ALERT_IDENTIFIER.FastTrack"
          }
        },
        "RISK_LEVEL": {
          "High": {
            "#HIGH": "@3,ALERT_IDENTIFIER.WFI_Reasons.@3,REASON_SRC_UNIQUE_ID.CustomerRiskLevel"
          },
          "Low": {
            "#LOW": "@3,ALERT_IDENTIFIER.WFI_Reasons.@3,REASON_SRC_UNIQUE_ID.CustomerRiskLevel"
          },
          "Medium": {
            "#MEDIUM": "@3,ALERT_IDENTIFIER.WFI_Reasons.@3,REASON_SRC_UNIQUE_ID.CustomerRiskLevel"
          }
        }
      }
    }
  },
  { //get rid of repetitions, eg. convert arrays to attributes 
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE",
        "WFI_Customer": {
          "*": {
            "*": "ONE"
          }
        },
        "WFI_Reasons": {
          "*": {
            "*": "ONE"
          }
        },
        "WF_Workitem_Link": {
          "*": {
            "*": "ONE"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[#2].WF_Workitem.&",
        "WFI_Customer": {
          "*": "[#3].WF_Workitem.WF_Instance.&1[]"
        },
        "WFI_Reasons": {
          "*": "[#3].WF_Workitem.WF_Instance.&1[]"
        },
        "WF_Workitem_Link": {
          "*": "[#3].WF_Workitem.&1[]"
        }
      }
    }
  }
]

(Edit)

Add the below spec after you spec in last.

Updated the spec this will hopefully resolve your query.

[
  {
    "operation": "modify-default-beta",
    "spec": {
      "*": {
        "WF_Workitem": {
          "WF_Instance": {
            "WFI_Customer": {
              "*": {
                "VAR": "=concat(@(1,CustomerID),@(1,CustomerSegment),@(1,CustomerTypeCode))"
              }
            },
            "WFI_Reasons": {
              "*": {
                "VAR": "=concat(@(1,CustomerRiskLevel),@(1,DetectionScore),@(1,DetectionTimeStamp),@(1,ReasonDescription),@(1,ReasonTimePeriod),@(1,SRC_AccountID),@(1,SRC_ID),@(1,SRC_TransactionID),@(1,ScenarioName))"
              }
            }
          }
        }
      }
    }
  }, {
    "operation": "shift",
    "spec": {
      "*": {
        "WF_Workitem": {
          "*": "[#3].WF_Workitem.&",
          "WF_Instance": {
            "*": "[#4].WF_Workitem.WF_Instance.&",
            "WFI_Customer": {
              "*": {
                "*": "[#6].WF_Workitem.WF_Instance.var.@(1,VAR).[#2].&"
              }
            },
            "WFI_Reasons": {
              "*": {
                "*": "[#6].WF_Workitem.WF_Instance.varReasons.@(1,VAR).[#2].&"
              }
            }
          }
        }
      }
    }
  }, {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "WF_Workitem": {
          "WF_Instance": {
            "var": "=recursivelySquashNulls",
            "varReasons": "=recursivelySquashNulls"
          }
        }
      }
    }
  }, {
    "operation": "cardinality",
    "spec": {
      "*": {
        "WF_Workitem": {
          "WF_Instance": {
            "var": {
              "*": "ONE"
            },
            "varReasons": {
              "*": "ONE"
            }
          }
        }
      }
    }
  }, {
    "operation": "shift",
    "spec": {
      "*": {
        "WF_Workitem": {
          "*": "[#3].WF_Workitem.&",
          "WF_Instance": {
            "*": "[#4].WF_Workitem.WF_Instance.&",
            "var": {
              "*": "[#5].WF_Workitem.WF_Instance.WFI_Customer"
            },
            "varReasons": {
              "*": "[#5].WF_Workitem.WF_Instance.WFI_Reasons"
            }
          }
        }
      }
    }
  }
]

Updated the spec :

First Shift operation -> Creates a new field named "VAR" inside WFI_Customer & WFI_Reasons array object which will consist of value of each attribute inside respective array to create a unique key object.
Second Shift operation -> On the basis of VAR field created, we will separate each array object into different objects. If duplicates are there then the object will be coming under same objects. (Debug this with input provided separately will give more context.)
Third operation will remove any nulls created because of previous operations. modify-overwrite-beta(recursivelySquashNulls)
Next operation cardinality will be applied to individual objects created in step 2 to remove any duplicates and give only unique value objects.
Last Shift Operation is to combine and form the previous objects from unique object arrays created from step 4 WFI_Customer & WFI_Reasons.

Hope this resolves your query.
Please accept the answer if this helps.

Please signup or login to give your own answer.

Click here to cancel reply.