Flattening and Mapping Complex JSON
We have a complex JSON object which has the columns information in one array and the row values in another. We would like the output to simply have the merged structure with value of the “Title” att...
Forum Discussion
Sorry should have provided the structure too
More like this:
[
[
{
“newRows”: [
{
“rowNumber”: 1,
“ORG_VALUE_SRC”: “1400076”,
“ORG_VALUE_TGT”: “1400076GBP”,
“CREATE_DATE”: “2018-02-28”,
“UPDATE_DATE”: “2018-02-28”
},
{
“rowNumber”: 2,
“ORG_VALUE_SRC”: “1400076”,
“ORG_VALUE_TGT”: “1400076EUR”,
“CREATE_DATE”: “2018-02-28”,
“UPDATE_DATE”: “2018-02-28”
},
{
“rowNumber”: 3,
“ORG_VALUE_SRC”: “1400077”,
“ORG_VALUE_TGT”: “1400077GBP”,
“CREATE_DATE”: “2018-02-28”,
“UPDATE_DATE”: “2018-02-28”
}
]
}
]
]
Basically a row for every source row using the “Title” as the key and “Value” from “myRows.cells” as the value. My need is grander than this but figured if someone could steer me in the right direction we could take it from there.
Thanks
Hi Brian,
I tried to achieve your requirement by using script snap.
readComplexJson_2018_12_26.slp (12.3 KB)
Thank you that helped and also validated that it needed to be a script block. Wan’t sure if there was any magic jsonPath or other Snap that could do this without scripting. Do you know if there are any limitations on using script blocks is large data streams? Just trying to understand best practices when using Script Blocks.
Hi Brain,
It seems to be tricky without using Script block but not sure, other Snaplogic expert can answer how to achieve using jsonPath or other snaps. i didn’t faced any problem while using script snap so i’m unable answer about limitations during large data streams .just one thing script snap is little bit time consuming.Thanks for the help, when you say time consuming, do you mean writing code or execution time?
The scripted gave us a good use case of writing a script snap.
I am levering this to read data from Smartsheet so plan to post the whole solution once complete, so thanks for your contribution. I know I can get the data from Smartsheet as a csv or other binary format but wanted to use the rich json format to add additional functionality like detecting change and data types.
Thanks again
