How to convert Nested JSON to Flattened JSON using JOLT transform

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