Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Jolt Transformation Nested Json to Flat Json

Jolt Transformation Nested Json to Flat Json

New Contributor

I am very close with this one. But, am afraid the output is not exactly what I am looking for. Can somebody please help me correct my Jolt Spec to get the exact transformation I am looking for? I have really tried cracking my brain at this and would really appreciate this last bit of help.

So currently I have that looks like this:

 

{
  "Hdr": {
    "DataSupplier": "BANK19000001",
    "FeedType": "Reuse",
    "SchemaVersion": "05.00",
    "SeqNo": "1",
    "DateProduced": "2019-09-30T11:44:12Z",
    "RecordCount": 1
  },
  "Data": [
    {
      "IM_SubmitterID": "BANK19000001",
      "IM_ParticipantID": "BANK19000001",
      "IM_AsOfDateTime": "2019-09-30T11:44:12Z",
      "IM_RepCpCodeType": "LEI",
      "CP_RepCp": "549300LW9VUUMJV5HC31",
      "CO_ActionType": "Normal",
      "CP_EntResponsibleForRep": "549300LW9VUUMJV5HC31",
      "LN_EventDate": "2019-09-30",
      "Clltrl": [
        {
          "CO_TypeOfCollComponent": "SECU",
          "CO_IdOfASecUsedAsColl": "FR0000131104",
          "RU_ValueOfReusedColl": "1000000",
          "RU_EstimatedReuseOfColl": "500000",
          "RU_ReusedCollCcy": "EUR"
        },
        {
          "CO_TypeOfCollComponent": "SECU",
          "CO_IdOfASecUsedAsColl": "FR0000131105",
          "RU_ValueOfReusedColl": "2000000",
          "RU_EstimatedReuseOfColl": "400000",
          "RU_ReusedCollCcy": "EUR"
        },
        {
          "CO_TypeOfCollComponent": "SECU",
          "CO_IdOfASecUsedAsColl": "FR0000131106",
          "RU_ValueOfReusedColl": "1500000",
          "RU_EstimatedReuseOfColl": "1000000",
          "RU_ReusedCollCcy": "EUR"
        },
        {
          "CO_TypeOfCollComponent": "CASH",
          "RU_ReinvestmentRate": "1.003",
          "RU_TypeOfReinvestedCashInvstmnt": "MMFT",
          "RU_ReinvestedCashAmt": "150000000",
          "RU_ReinvestedCashCcy": "USD"
        },
        {
          "CO_TypeOfCollComponent": "CASH",
          "RU_ReinvestmentRate": "0.05",
          "RU_TypeOfReinvestedCashInvstmnt": "REPM",
          "RU_ReinvestedCashAmt": "200000000",
          "RU_ReinvestedCashCcy": "EUR"
        }
      ],
      "FndngSrce": [
        {
          "RU_FundingSources": "OTHR",
          "RU_MarketValueOfTheFundingSources": "12000000",
          "RU_FundingSourcesCcy": "USD"
        },
        {
          "RU_FundingSources": "BSHS",
          "RU_MarketValueOfTheFundingSources": "150000000",
          "RU_FundingSourcesCcy": "EUR"
        }
      ],
      "UsrDfnd": {
        "IM_UserDefined1": "Test Data"
      }
    }
  ]
}

 

This is the current Jolt Specs I have currently written so far:

 

[
  {
    "operation": "shift",
    "spec": {
      "Hdr": {
        "*": "header.&"
      },
      "Data": {
        "*": {
          "*": {
            "*": {
              "*": "body.&"
            }
          }
        }
      }
    }
  }]

 

And the output after the transformation:

 

{
  "header" : {
    "DataSupplier" : "BANK19000001",
    "FeedType" : "Reuse",
    "SchemaVersion" : "05.00",
    "SeqNo" : "1",
    "DateProduced" : "2019-09-30T11:44:12Z",
    "RecordCount" : 1
  },
  "body" : {
    "CO_TypeOfCollComponent" : [ "SECU", "SECU", "SECU", "CASH", "CASH" ],
    "CO_IdOfASecUsedAsColl" : [ "FR0000131104", "FR0000131105", "FR0000131106" ],
    "RU_ValueOfReusedColl" : [ "1000000", "2000000", "1500000" ],
    "RU_EstimatedReuseOfColl" : [ "500000", "400000", "1000000" ],
    "RU_ReusedCollCcy" : [ "EUR", "EUR", "EUR" ],
    "RU_ReinvestmentRate" : [ "1.003", "0.05" ],
    "RU_TypeOfReinvestedCashInvstmnt" : [ "MMFT", "REPM" ],
    "RU_ReinvestedCashAmt" : [ "150000000", "200000000" ],
    "RU_ReinvestedCashCcy" : [ "USD", "EUR" ],
    "RU_FundingSources" : [ "OTHR", "BSHS" ],
    "RU_MarketValueOfTheFundingSources" : [ "12000000", "150000000" ],
    "RU_FundingSourcesCcy" : [ "USD", "EUR" ],
    "Test Data" : null
  }
}

 

This is really closed to the desired result. However, it is missing some data from the "Data" section of the input. I have literally tried every possible combination and have tried searching for an answer online, but to no avail.

Desired Json output:

 

{
"header" : {
"DataSupplier" : "BANK19000001",
"FeedType" : "Reuse",
"SchemaVersion" : "05.00",
"SeqNo" : "1",
"DateProduced" : "2019-09-30T11:44:12Z",
"RecordCount" : 1
},
"body" : {
"IM_SubmitterID" : "BANK19000001",
"IM_ParticipantID" : "BANK19000001",
"IM_AsOfDateTime" : "2019-09-30T11:44:12Z",
"IM_RepCpCodeType" : "LEI",
"CP_RepCp" : "549300LW9VUUMJV5HC31",
"CO_ActionType" : "Normal",
"CP_EntResponsibleForRep" : "549300LW9VUUMJV5HC31",
"LN_EventDate" : "2019-09-30",
"CO_TypeOfCollComponent" : [ "SECU", "SECU", "SECU", "CASH", "CASH" ],
"CO_IdOfASecUsedAsColl" : [ "FR0000131104", "FR0000131105", "FR0000131106" ],
"RU_ValueOfReusedColl" : [ "1000000", "2000000", "1500000" ],
"RU_EstimatedReuseOfColl" : [ "500000", "400000", "1000000" ],
"RU_ReusedCollCcy" : [ "EUR", "EUR", "EUR" ],
"RU_ReinvestmentRate" : [ "1.003", "0.05" ],
"RU_TypeOfReinvestedCashInvstmnt" : [ "MMFT", "REPM" ],
"RU_ReinvestedCashAmt" : [ "150000000", "200000000" ],
"RU_ReinvestedCashCcy" : [ "USD", "EUR" ],
"RU_FundingSources" : [ "OTHR", "BSHS" ],
"RU_MarketValueOfTheFundingSources" : [ "12000000", "150000000" ],
"RU_FundingSourcesCcy" : [ "USD", "EUR" ],
"IM_UserDefined1" : "Test Data"
}
}

 


Can somebody please help me modify my Jolt Specs to achieve the desired result.

Any help is much appreciated.

 

 

 

 

1 REPLY 1
Highlighted

Re: Jolt Transformation Nested Json to Flat Json

Contributor

@Branana Have a look at my jolt spec!

[
{
"operation": "shift",
"spec": {
"Hdr": "header",
"Data": {
"*": {
"Clltrl": {
"*": {
"*": "body.&[]"
}
},
"FndngSrce": {
"*": {
"*": "body.&[]"
}
},
"UsrDfnd": {
"*": "body.&"
},
"*": "body.&"
}
}
}
}
]
Don't have an account?
Coming from Hortonworks? Activate your account here