Trying to flatten the hierarchical json

Hello, Below is the JSON output from a REST API source. I am trying to flatten the hierarchical JSON structure using the splitter snap. I am able to flatten “meals” attribute, but unable to split the “credentials”. The expected output should be all the attributes should be column names.

{
“object”: “list”,
“data”: [
{
“id”: “locid”,
“object”: “locname”,
“group_id”: “grp_id”,
“name”: “name1”,
“active”: 1,
“count”: 2,
“timezone”: “xxxx”,
“week_end”: “Wednesday”,
“sales_tax”: 0.0825,
“schedule_start”: “”,
“schedule_stop”: “”,
“type”: “xxxx”,
“meals”: [
{
“name”: “breakfast”,
“end”: “10:30”
},
{
“name”: “lunch”,
“end”: “15:00”
}
],
“credentials”: {
“str_id”: “999999”,
“str_id_2”: “2222”
}
}

You can use a Structure snap to flatten a JSON object. You can’t use Splitter on credentials because it’s not an array.

Can you export and attach the pipeline that you have so far so that we can see the configuration of the splitter snap?

I think this example may accomplish what you’re trying to do? Split JSON Object.slp (6.9 KB)

I tried the structure snap and is giving me the same result where i am not able to split the “credentials” as it is not an array. Not sure what i am doing wrong.

Did you look at my example?

Sorry. I just saw your reply with example. Will go through it and get back to you. Thanks

1 Like

This is actually a pretty simple restructuring, so you could also just replace the Structure snap with a Mapper snap with the same source/target values and pass through checked as well.

Splitjsonobject.slp (13.8 KB)

Uploaded the Pipeline file “splitjsonobject.slp”

Uploaded the Pipeline file "splitjsonobject.slp. Not able to get the desired resultset.

Splitjsonobject.slp (13.8 KB)

@arvindnsn Can you clarify how the fields in the “credentials” object are supposed to be flattened? Is “str_id” supposed to be paired with the first element of the “meals” array and is “str_id_2” supposed to be paired with the second element from the “meals” array?

For example, are you expecting two output documents that look something like this:

{
“name”: “breakfast”,
“end”: “10:30”,
"str_id": "999999",
...
}
{
“name”: “lunch”,
“end”: “15:00”,
"str_id": "2222",
...
}

If you can give the exact expected output, it would make things easier.

Thanks

I want the credentials attributes to be seperate columns for each document.

For example, the JSOn has 2 documents as input and the expected result in tabular format would be as below

Loc_id, store_id, store_id2, stadis (this attribute may or may not be in few documents

I’m attaching an updated version of the pipeline you supplied. But, I’m not sure if it’s doing what you want. I just used a mapper at the end to flatten out the credentials object. Also, since the second document the JSONGenerator had an empty “meals” array, the splitter is not going to output for that. Is that what you’re looking for or did you still want a row to come out?

Revenue_Summary_Mashgin.stg_2018_10_11.slp (11.7 KB)

The attached example is able to flatten the Credentials attribute. But If you see the output results, It is not bringing any data for the 2nd document (ie Loc_2), since it does not have any meal information

You’ll need to add some element to the meal list when it’s empty in order to get the splitter to do anything. Maybe add an empty object (i.e. {}) or null.

That Worked. Thank You.