Question

Source: https://stackoverflow.com/questions/71038813/convert-complex-nested-json-array-using-jolt-expression-in-nifi

I have a complex JSON array which I wanted to flatten using Jolt or any other way in NiFi. Can anyone please help to flatten the following JSON?

{

"veread": "ndjdjjywuieopppqpppwaghhzuii",

"debittanceInformation": [

{

"internalDocumentInformation": [

{

"number": "8",

"lineDetails": [

{

"identifications": [

{

"number": "44",

"model": {

"code": "xdd",

"proprietary": "ddy"

},

"relatedDate": "3/4"

}

],

"quantity": 136,

"unitOfMeasure": "each",

"money": {

"duePayablemoney": 44,

"discountAppliedmoneys": [

{

"model": {

"code": "dddddd",

"proprietary": "rddddy"

},

"money": 216.2

}

],

"valuemoneys": [

{

"model": {

"code": "ddddd",

"proprietary": "dddd"

},

"money": 391.41

}

],

"adjustmentmoneyAndReasons": [

{

"money": 824.54,

"reason": "dd"

}

],

"debittedmoney": 660.7,

"creditNotemoney": 985.63

}

}

],

"internalDocumentmoney": {

"duePayablemoney": 567.45,

"discountAppliedmoneys": [

{

"model": {

"code": "DD",

"proprietary": "EE"

},

"money": 5.64

}

],

"valuemoneys": [

{

"model": {

"code": "z",

"proprietary": "a"

},

"money": 145

}

],

"adjustmentmoneyAndReasons": [

{

"money": 678,

"reason": "tyuiop"

}

],

"debittedmoney": 123,

"creditNotemoney": 456

}

}

],

"supplierReferenceInformation": {

"model": {

"code": "x",

"proprietary": "y"

},

"issuer": "issuer",

"reference": "hfhjfdozkdfl;fdko;k"

},

"billr": {

"name": "jdjaiofjiosJOfjaiodjoiwaj",

"companyIdentifications": [

{

"identification": "74747435438",

"model": "jdidfjdsjf"

}

]

},

"bille": {

"name": "djhsaijdwjadfja;dddF",

"companyIdentifications": [

{

"identification": "dddddd",

"model": "kksjdadawx"

}

]

},

"additionaldebittanceInformation": [

"dd",

"xnjasndfjafnlkNDflkhlfkacmakjhfncasklnf"

]

}

],

"chargesInformation": []

}

The above is My JSON which I need to flatten into simple JSON so that I can process it in Hive. I have tried using Flatten JSON processor but it is not working, so now I am trying using Jolt spec. Can anyone please guide me with above issue using either Jolt transformation or in NiFi.

My expected output should be as follows:

{

"veread": "ndjdjjywuieopppqpppwaghhzuii",

"ddddwww_internalDocumentInformation_number": "8",

"ddddwww_internalDocumentInformation_lineDetails_identifications_number": "1025",

"ddddwww_internalDocumentInformation_lineDetails_identifications_model.code": "x",

"ddddwww_internalDocumentInformation_lineDetails_identifications_model.proprietary": "y",

"ddddwww_internalDocumentInformation_lineDetails_identifications_relatedDate": "3/23",

"ddddwww_internalDocumentInformation_lineDetails_quantity": 136,

"ddddwww_internalDocumentInformation_lineDetails_unitOfMeasure": "each",

"ddddwww_internalDocumentInformation_lineDetails_money.duePayablemoney": 957.86,

"ddddwww_internalDocumentInformation_lineDetails_money.discountAppliedmoneys_model.code": "dvbnqwe",

"ddddwww_internalDocumentInformation_lineDetails_money.discountAppliedmoneys_model.proprietary": "rqazloy",

"ddddwww_internalDocumentInformation_lineDetails_money.discountAppliedmoneys_money": 216.2,

"ddddwww_internalDocumentInformation_lineDetails_money.valuemoneys_model.code": "YUIO",

"ddddwww_internalDocumentInformation_lineDetails_money.valuemoneys_model.proprietary": "BHRTY",

"ddddwww_internalDocumentInformation_lineDetails_money.valuemoneys_money": 391.41,

"ddddwww_internalDocumentInformation_lineDetails_money.adjustmentmoneyAndReasons_money": 824.54,

"ddddwww_internalDocumentInformation_lineDetails_money.adjustmentmoneyAndReasons_reason": "BONUS",

"ddddwww_internalDocumentInformation_lineDetails_money.debittedmoney": 660.7,

"ddddwww_internalDocumentInformation_lineDetails_money.creditNotemoney": 985.63,

"ddddwww_internalDocumentInformation_internalDocumentmoney.duePayablemoney": 567.45,

"ddddwww_internalDocumentInformation_internalDocumentmoney.discountAppliedmoneys_model.code": "DD",

"ddddwww_internalDocumentInformation_internalDocumentmoney.discountAppliedmoneys_model.proprietary": "EE",

"ddddwww_internalDocumentInformation_internalDocumentmoney.discountAppliedmoneys_money": 5.64,

"ddddwww_internalDocumentInformation_internalDocumentmoney.valuemoneys_model.code": "z",

"ddddwww_internalDocumentInformation_internalDocumentmoney.valuemoneys_model.proprietary": "a",

"ddddwww_internalDocumentInformation_internalDocumentmoney.valuemoneys_money": 145,

"ddddwww_internalDocumentInformation_internalDocumentmoney.adjustmentmoneyAndReasons_money": 678,

"ddddwww_internalDocumentInformation_internalDocumentmoney.adjustmentmoneyAndReasons_reason": "tyuiop",

"ddddwww_internalDocumentInformation_internalDocumentmoney.debittedmoney": 123,

"ddddwww_internalDocumentInformation_internalDocumentmoney.creditNotemoney": 456,

"ddddwww_supplierReferenceInformation.model.code": "x",

"ddddwww_supplierReferenceInformation.model.proprietary": "y",

"ddddwww_supplierReferenceInformation.issuer": "issuer",

"ddddwww_supplierReferenceInformation.reference": "hfhjfdozkdfl;fdko;k",

"ddddwww_billr.name": "jdjaiofjiosJOfjaiodjoiwaj",

"ddddwww_billr.companyIdentifications_identification": "74747435438",

"ddddwww_billr.companyIdentifications_model": "jdidfjdsjf",

"ddddwww_bille.name": "djhsaijdwjadfja;lMEjknEAKDLJOJRF",

"ddddwww_bille.companyIdentifications_identification": "mkskfsajdwiojdijRUWa",

"ddddwww_bille.companyIdentifications_model": "kksjdadawx",

"ddddwww_additionalddddwww[0]": "JDSJAHDJKLJHRFQWJESJqiorwjd",

"ddddwww_additionalddddwww[1]": "xnjasndfjafnlkNDflkhlfkacmakjhfncasklnf",

"chargesInformation": []

}

I have updated the request.

Answer

The Java code of flattening JSON records of an indefinite number of levels to a two-dimensional table is extremely long.

You can use SPL, an open-source Java package, to get this done. It is convenient and four lines of code are sufficient:

	A	B
1	=i=0,json(file("data.json").read())
2	func recurse(r)	>i+=1,r.fno().run(tmp=eval("r.#"/~),B1=B1.to(:i-1)|r.fname(~),if(ifr(tmp)   ||   ift(tmp),func(recurse,tmp),if(ifa(tmp),if(tmp==[],C1|=("\""/B1.concat("_")/"\":"/tmp),tmp.run(if(ifstring(~),C1|=("\""/B1.concat("_")/"["/(#-1)/"]\":   \""/~/"\""),C1|=("\""/B1.concat("_")/"["/(#-1)/"]\":"/~)))),if(ifstring(tmp),C1|=("\""/B1.concat("_")/"\":   \""/tmp/"\""),C1|=("\""/B1.concat("_")/"\":"/tmp)))))
3	=func(recurse,A1)
4	="{"/C1.concat@c()/"}"

 

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as flattenjson.splx and invoke it in Java as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st = con.prepareCall("call flattenjson()");
st.execute();

…

View SPL source code.