convert complex nested json array using jolt expression in nifi
问题
I have a complex Json where i wanted to flatten the Json using Jolt or any other ways to flatten in Nifi is also fine can anyone please help to flatten the below 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 in to 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 an trying using jolt spec can any one please guide me with above issue using either Jolt transformation or in Nifi.
my expected output should be as below
{
"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
解答
将不确定层级的json扁平化成二维表。用Java 实现代码较长。
用Java 下的开源包 SPL 很容易写，只要 4行：
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 提供了 JDBC 供 Java 调用，把上面的脚本存为 flattenjson.splx，在 Java 中以存储过程的方式调用脚本文件：
…
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
st = con.prepareCall("call flattenjson()");
st.execute();
…
English version