question Re: How to convert Nested JSON to Flattened JSON using JOLT transform in Support Questions

How to convert Nested JSON to Flattened JSON using JOLT transform

Sid17 — Wed, 11 Dec 2024 09:14:10 GMT

Hi guys,

I want to convert Nested JSON to Flattened JSON using JOLT transform.

Nested JSON:

{
"data": {
"getBusinessRelationListing": {
"edges": [

{

"node": {

"id": "1964730",

"name": null,

"code": "CN02",

"bejoCompany": true,

"legacyVendorId": [],

"locatonType": [

"Commercial"

"countries": [

{

"locationCountry": "CN",

"countryCode": "CN",

"name": "China",

"parent": {

"shortCode": "AMEA",

"name": "Africa & Middle East & Asia"

"region": ""

}

]

}

"node": {
"id": "1964732",
"name": null,
"code": "CZ01",
"bejoCompany": true,
"legacyVendorId": [],
"locatonType": [
"Commercial"
],
"countries": [
{
"locationCountry": "CZ",
"countryCode": "CZ",
"name": "Czechia",
"parent": {
"shortCode": "EU",
"name": "Europe"
},
"region": "NH"
},
{
"locationCountry": "SK",
"countryCode": "SK",
"name": "Slovakia",
"parent": {
"shortCode": "EU",
"name": "Europe"
},
"region": "NH"
}
]
}
}
]
}
}
}

I am using this JOLT Spec:

[
{
"operation": "shift",
"spec": {
"data": {
"getBusinessRelationListing": {
"edges": {
"*": {
"node": {
"id": "[&2].Id",
"code": "[&2].BusinessRelation",
"name": "[&2].BusinessRelationDisplayName",
"countries": {
"*": {
"locationCountry": "[&4].LocationCountry",
"parent": {
"name": "[&5].LocationCountryName",
"shortCode": "[&5].LocationRegion"
}
}
}
}
}
}
}
}
}
}
]
It is giving output in this format:
[ {
"BusinessRelation" : "CN02",
"BusinessRelationDisplayName" : null,
"Id" : "1964730",
"LocationCountry" : "CN",
"LocationCountryName" : "Africa & Middle East & Asia",
"LocationRegion" : "AMEA"
}, {
"BusinessRelation" : "CZ01",
"BusinessRelationDisplayName" : null,
"Id" : "1964732",
"LocationCountry" : [ "CZ", "SK" ],
"LocationCountryName" : [ "Europe", "Europe" ],
"LocationRegion" : [ "EU", "EU" ]
} ]

But I want output in the below format:

[ {
"BusinessRelation" : "CN02",
"BusinessRelationDisplayName" : null,
"Id" : "1964730",
"LocationCountry" : "CN",
"LocationCountryName" : "Africa & Middle East & Asia",
"LocationRegion" : "AMEA"
}, {
"BusinessRelation" : "CZ01",
"BusinessRelationDisplayName" : null,
"Id" : "1964732",
"LocationCountry" : "CZ" ,
"LocationCountryName" : "Europe",
"LocationRegion" : "EU",
} ,

{
"BusinessRelation" : "CZ01",
"BusinessRelationDisplayName" : null,
"Id" : "1964732",
"LocationCountry" : "SK" ,
"LocationCountryName" : "Europe",
"LocationRegion" : "EU",
} ]

I'm new to JOLT. Please help me

thank you

Re: How to convert Nested JSON to Flattened JSON using JOLT transform

VidyaSargur — Wed, 11 Dec 2024 08:51:57 GMT

@Sid17, Welcome to our community! To help you get the best possible answer, I have tagged our NiFi experts @MattWho @SAMSAL @steven-matison who may be able to assist you further.

Please feel free to provide any additional information or details about your query, and we hope that you will find a satisfactory solution to your question.

Re: How to convert Nested JSON to Flattened JSON using JOLT transform

Sid17 — Wed, 11 Dec 2024 09:03:38 GMT

Hi @VidyaSargur ,

Thank you so much!

Re: How to convert Nested JSON to Flattened JSON using JOLT transform

SAMSAL — Wed, 11 Dec 2024 14:28:44 GMT

Hi @Sid17 ,

Welcome to the community. Since you are new let me recommend couple of tips for future posts that will help you get better attention and response time from the community:

1- When posting json format data or any other format please make sure the format is valid. Im saying this because neither the input nor the expected output is in valid format.

2- When posting code or data in certain format please try to use the Insert/Edit Code Sample with the symbol "</>" from the tool bar. If you dont see it just expand the tool bar by clicking the (...). This will help your code\data standout by applying proper formatting and color coding - as you will see below- depending on the code\format you select from the drop down.

Regarding your jolt problem , I can see from the spec that you are still learning. Jolt can get very confusing and you will get better as you practice. I would recommend you to first explore some tutorial to you help you at least understand the basics like:

https://community.cloudera.com/t5/Community-Articles/Jolt-quick-reference-for-Nifi-Jolt-Processors/ta-p/244350

https://docs.digibee.com/documentation/components/tools/transformer-jolt

https://github.com/bazaarvoice/jolt

As for the spec, you can try the following:

[ { "operation": "shift", "spec": { "data": { "getBusinessRelationListing": { "edges": { "*": { "node": { "countries": { "*": { "@(2,code)": "[&4].[&1].BusinessRelation", "@(2,id)": "[&4].[&1].Id", "@(2,name)": "[&4].[&1].BusinessRelationDisplayName", "locationCountry": "[&4].[&1].&", "parent": { "name": "[&5].[&2].LocationCountryName", "shortCode": "[&5].[&2].LocationRegion" } } } } } } } } } }, { "operation": "shift", "spec": { "*": { "*": { "@": "[]" } } } } ]

Notice the spec uses two shift transformations:

1- Map all the fields accordingly and group according to their array & sub array position from the input.

2- get all objects from the transformation above and bucket into new array.

Also, since you are beginner in the Json Transformation. I would recommend you to take a look at another json transformation language called JSLT which Nifi has processor for (version 127.0+). JSTL is very powerful transformation language as well and it works similar to XQuery if you are familiar with that. I like to use it in these situation because its easier to traverse nested structure with and flatten it, therefore it will be much less line of codes and more readable than jolt as in the following:

let X = [for (.data.getBusinessRelationListing.edges) let node = (.node) [for (.node.countries) { "BusinessRelation":$node.code, "Id":$node.id, "BusinessRelationDisplayName":$node.name, "locationCountry": .locationCountry, "LocationCountryName":.parent.name, "LocationRegion":.parent.shortCode } ] ] flatten($X)

For more information regarding JST, please refer to:

https://github.com/schibsted/jslt/tree/master

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-jslt-nar/1.27.0/org.apache.nifi.processors.jslt.JSLTTransformJSON/index.html

Hope that helps. if it does, please accept the solution.

Thanks

Re: How to convert Nested JSON to Flattened JSON using JOLT transform

Sid17 — Thu, 12 Dec 2024 05:15:08 GMT

Hi Samsal,

Firstly I want to thank you for taking your time in solving my query. The solution you provided worked like a magic.

Secondly, yes I am new to this platform and also for JOLT, moving forward I will follow your tips and suggestions and will go through the courses which you've shared.

Once again thank you for your valuable assistance. It made a significant difference. I am grateful.