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.

JoltTransformation Json-to-Json conversion in Nifi??

JoltTransformation Json-to-Json conversion in Nifi??

Explorer

Hello,

1) I am fetching data from relational database and then convert that data into flat json.

2)split json file using splitJson processor and then convert json file into nested json(that part working properly).

Input json

{"RqUID":"UID.1","RqCreatedDt":"2017-06-22T13:10:03Z","RqMsgUID":"Kp-gp1-acc-1.3","RqType":"ACCTTRNHISTADD","RqMsgCreatedDt":"2017-06-22T13:10:03Z","AcctTrnId":"Kp-gp1-acc-1-trn.4","TrnType":"POSTED SALE","TrnSrc":"PURCHASE - AT TERMINAL","MCC":"4112","Memo":"commuter","Amt":"180000.0","AmtType":"DR","CurCode":"IDR","FXTrnInd":"false","AcctId":"Kp-gp1-acc-1","AcctType":"CCA","StatusCode":"POSTED","OrigDt":"2018-04-26T04:00:00.100Z"}

Jolt Specification

[ { "operation": "shift", "spec": { "RqUID": "RqHdr.RqUID", "RqCreatedDt": "RqHdr.RqCreatedDt", "RqMsgUID": "RqMsg[0].RqMsgUID", "RqType": "RqMsg[0].RqType", "RqMsgCreatedDt": "RqMsg[0].RqMsgCreatedDt", "AcctTrnId": "RqMsg[0].AcctTrnRec.AcctTrnId", "TrnType": "RqMsg[0].AcctTrnRec.AcctTrnInfo.TrnDetails.TrnType", "TrnSrc": "RqMsg[0].AcctTrnRec.AcctTrnInfo.TrnDetails.TrnSrc", "MCC": "RqMsg[0].AcctTrnRec.AcctTrnInfo.TrnDetails.MCC", "Memo": "RqMsg[0].AcctTrnRec.AcctTrnInfo.Memo", "Amt": "RqMsg[0].AcctTrnRec.AcctTrnInfo.TrnAmtInfo.Amt", "AmtType": "RqMsg[0].AcctTrnRec.AcctTrnInfo.TrnAmtInfo.AmtType", "CurCode": "RqMsg[0].AcctTrnRec.AcctTrnInfo.TrnAmtInfo.CurCode", "FXTrnInd": "RqMsg[0].AcctTrnRec.AcctTrnInfo.FXTrnInd", "AcctId": "RqMsg[0].AcctTrnRec.AcctTrnInfo.TrnAcctInfo.AcctId", "AcctType": "RqMsg[0].AcctTrnRec.AcctTrnInfo.TrnAcctInfo.AcctType", "StatusCode": "RqMsg[0].AcctTrnRec.AcctTrnStatus.StatusCode", "OrigDt": "RqMsg[0].AcctTrnRec.AcctTrnStatus.OrigDt" } } ]

output json

{"RqHdr":{"RqUID":"UID.1","RqCreatedDt":"2017-06-22T13:10:03Z"},"RqMsg":[{"RqMsgUID":"Kp-gp1-acc-1.3","RqType":"ACCTTRNHISTADD","RqMsgCreatedDt":"2017-06-22T13:10:03Z","AcctTrnRec":{"AcctTrnId":"Kp-gp1-acc-1-trn.4","AcctTrnInfo":{"TrnDetails":{"TrnType":"POSTED SALE","TrnSrc":"PURCHASE - AT TERMINAL","MCC":"4112"},"Memo":"commuter","TrnAmtInfo":{"Amt":"180000.0","AmtType":"DR","CurCode":"IDR"},"FXTrnInd":"false","TrnAcctInfo":{"AcctId":"Kp-gp1-acc-1","AcctType":"CCA"}},"AcctTrnStatus":{"StatusCode":"POSTED","OrigDt":"2018-04-26T04:00:00.100Z"}}}]}

3) Problem

I want direct convert flat array json into the nested json one show as bellow

a) when trying to convert array of flat json file into nested json array file,i am getting output null

nested array flat input json

[{"RqUID":"UID.1","RqCreatedDt":"2017-06-22T13:10:03Z","RqMsgUID":"Kp-gp1-acc-1.3","RqType":"ACCTTRNHISTADD","RqMsgCreatedDt":"2017-06-22T13:10:03Z","AcctTrnId":"Kp-gp1-acc-1-trn.4","TrnType":"POSTED SALE","TrnSrc":"PURCHASE - AT TERMINAL","MCC":"4112","Memo":"commuter","Amt":"180000.0","AmtType":"DR","CurCode":"IDR","FXTrnInd":"false","AcctId":"Kp-gp1-acc-1","AcctType":"CCA","StatusCode":"POSTED","OrigDt":"2018-04-26T04:00:00.100Z"},{"RqUID":"UID.1.kp.gp1-acc-1.01","RqCreatedDt":"2017-06-22T13:10:03Z","RqMsgUID":"Kp-gp1-acc-1.3","RqType":"ACCTTRNHISTADD","RqMsgCreatedDt":"2017-06-22T13:10:03Z","AcctTrnId":"Kp-gp1-acc-1-trn.4","TrnType":"POSTED SALE","TrnSrc":"PURCHASE - AT TERMINAL","MCC":"4112","Memo":"commuter","Amt":"180000.0","AmtType":"DR","CurCode":"IDR","FXTrnInd":"false","AcctId":"Kp-gp1-acc-1","AcctType":"CCA","StatusCode":"POSTED","OrigDt":"2018-04-26T04:00:00.100Z"}]

Expected Output

{"RqHdr":{"RqUID":"UID.1","RqCreatedDt":"2017-06-22T13:10:03Z"},"RqMsg":[{"RqMsgUID":"Kp-gp1-acc-1.3","RqType":"ACCTTRNHISTADD","RqMsgCreatedDt":"2017-06-22T13:10:03Z","AcctTrnRec":{"AcctTrnId":"Kp-gp1-acc-1-trn.4","AcctTrnInfo":{"TrnDetails":{"TrnType":"POSTED SALE","TrnSrc":"PURCHASE - AT TERMINAL","MCC":"4112"},"Memo":"commuter","TrnAmtInfo":{"Amt":"180000.0","AmtType":"DR","CurCode":"IDR"},"FXTrnInd":"false","TrnAcctInfo":{"AcctId":"Kp-gp1-acc-1","AcctType":"CCA"}},"AcctTrnStatus":{"StatusCode":"POSTED","OrigDt":"2018-04-26T04:00:00.100Z"}}},{"RqMsgUID":"Kp-gp1-acc-1.3","RqType":"ACCTTRNHISTADD","RqMsgCreatedDt":"2017-06-22T13:10:03Z","AcctTrnRec":{"AcctTrnId":"Kp-gp1-acc-1-trn.4","AcctTrnInfo":{"TrnDetails":{"TrnType":"POSTED SALE","TrnSrc":"PURCHASE - AT TERMINAL","MCC":"4112"},"Memo":"commuter","TrnAmtInfo":{"Amt":"180000.0","AmtType":"DR","CurCode":"IDR"},"FXTrnInd":"false","TrnAcctInfo":{"AcctId":"Kp-gp1-acc-1","AcctType":"CCA"}},"AcctTrnStatus":{"StatusCode":"POSTED","OrigDt":"2018-04-26T04:00:00.100Z"}}}]}

Trying from long time but not getting exact solution, can some one help.

Thanks

@Shu

@Gonçalo Cunha

avatar image

Don't have an account?
Coming from Hortonworks? Activate your account here