Flattening and Mapping Complex JSON

[murphy-brian]

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” attribute held in the “myCols” as the key and the value from the “myRows.Cells.value” mapped.

We would like a row for each myRows element. We’d like to do this without using the absolute index numbers of either and leverage the “myCols.id” and “myRows.cells.columnId” which are how they align. The purpose of this is to have a dynamic source and which can have any number of columns that could eventually create a results table and insert the rows.

Any ideas?

[
{
“myCols”: [
{
“id”: 7169064356341636,
“version”: 0,
“index”: 0,
“title”: “ORG_VALUE_SRC”,
“type”: “TEXT_NUMBER”,
“primary”: true,
“validation”: false,
“width”: 150
},
{
“id”: 1539564822128516,
“version”: 0,
“index”: 1,
“title”: “ORG_VALUE_TGT”,
“type”: “TEXT_NUMBER”,
“validation”: false,
“width”: 150
},
{
“id”: 6043164449499012,
“version”: 0,
“index”: 2,
“title”: “CREATE_DATE”,
“type”: “TEXT_NUMBER”,
“validation”: false,
“width”: 150
},
{
“id”: 3791364635813764,
“version”: 0,
“index”: 3,
“title”: “UPDATE_DATE”,
“type”: “DATE”,
“validation”: false,
“width”: 150
}
],
“myRows”: [
{
“id”: 7737866304415620,
“rowNumber”: 1,
“expanded”: true,
“createdAt”: “2018-12-18T17:48:08Z”,
“modifiedAt”: “2018-12-18T17:48:08Z”,
“cells”: [
{
“columnId”: 7169064356341636,
“value”: 1400076,
“displayValue”: “1400076”
},
{
“columnId”: 1539564822128516,
“value”: “1400076GBP”,
“displayValue”: “1400076GBP”
},
{
“columnId”: 6043164449499012
},
{
“columnId”: 3791364635813764,
“value”: “2018-02-28”
}
]
},
{
“id”: 2108366770202500,
“rowNumber”: 2,
“siblingId”: 7737866304415620,
“expanded”: true,
“createdAt”: “2018-12-18T17:48:08Z”,
“modifiedAt”: “2018-12-18T17:48:08Z”,
“cells”: [
{
“columnId”: 7169064356341636,
“value”: 1400076,
“displayValue”: “1400076”
},
{
“columnId”: 1539564822128516,
“value”: “1400076EUR”,
“displayValue”: “1400076EUR”
},
{
“columnId”: 6043164449499012
},
{
“columnId”: 3791364635813764,
“value”: “2018-02-28”
}
]
},
{
“id”: 6611966397572996,
“rowNumber”: 3,
“siblingId”: 2108366770202500,
“expanded”: true,
“createdAt”: “2018-12-18T17:48:08Z”,
“modifiedAt”: “2018-12-18T17:48:08Z”,
“cells”: [
{
“columnId”: 7169064356341636,
“value”: 1400077,
“displayValue”: “1400077”
},
{
“columnId”: 1539564822128516,
“value”: “1400077GBP”,
“displayValue”: “1400077GBP”
},
{
“columnId”: 6043164449499012
},
{
“columnId”: 3791364635813764,
“value”: “2018-02-28”
}
]
}
]
}
]

[Ajay_Chawda]

Hi Brian,
Below screen shot you are trying to achieve?

[murphy-brian]

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

[Ajay_Chawda]

Hi Brian,
I tried to achieve your requirement by using script snap.

readComplexJson_2018_12_26.slp (12.3 KB)

[murphy-brian]

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.