Trying to flatten the hierarchical json

[arvindnsn]

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”
}
}

[Schevus]

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.

[arvindnsn]

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

Splitjsonobject.slp (13.8 KB)

[tstack]

@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

[arvindnsn]

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

[tstack]

arvindnsn:

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

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)