cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Flatten multilevel/nested JSON Objects and Arrays

ionutbarna
New Contributor II

Hello there,

Is there a way to flatten a nested JSON (multiple objects inside objects/arrays) without using a Script Snap?

Take this input for example:

{
   "msg":"Hello, World",
   "num":1,
   "fruits":{
      "apple":1,
      "orange":4,
      "inner2":{
         "message":"found"
      },
      "cars":[
         "bmw",
         "audi",
         "dacia"
      ],
      "cars2":[
         {
            "bmw":"something"
         },
         {
            "audi":"else"
         }
      ]
   }
}

The output should look like this:

{
  "msg": "Hello, World",
  "fruits.cars2.1.audi": "else",
  "fruits.orange": 4,
  "num": 1,
  "fruits.cars.1": "audi",
  "fruits.cars.0": "bmw",
  "fruits.apple": 1,
  "fruits.cars.2": "dacia",
  "fruits.inner2.message": "found",
  "fruits.cars2.0.bmw": "something"
}

So every key name is constructed from the inner objects/arrays of key names delimited with โ€œ.โ€ in the case of objects. In the case of arrays, the key name should also have the index number of the current item.

One way to do this is using the script snap using any available language. A function that loops through the root object and check the type of the current property. If itโ€™s of type Object or array then I can call the function again.

Iโ€™ve tried using expression libraries but with no luck.

Any ideas?

4 REPLIES 4

hemangjalali
New Contributor II

Any luck on this? I am trying to find a solution for a similar problem

tlikarish
Employee
Employee

There is the AutoPrep snap that can handle this transformation. Do you have access to that?

https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/2568781882/AutoPrep

Wow! That's a powerful snap! Worked like a charm for my use case.
Thank you for the suggestion.

Indeed it is! This snap can flatten structures, split columns, mask data, rename columns, handle nulls, and more, much faster than relying on python or expressions.