Forum Discussion

murphy-brian's avatar
murphy-brian
New Contributor III
7 years ago

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

9 Replies

  • tstack's avatar
    tstack
    Former Employee
    7 years ago

    You should be able to do this through the expression language using the .extend() method to construct objects and the .map() method to reshape the elements of the arrays.

    Here’s a pipeline with your input data to demonstrate:
    FlattenJson_2018_12_26.slp (11.6 KB)

    In that pipeline, the first mapper converts the column array to an object indexed by the column IDs to make it easier to do that lookup. The expression in that mapper looks like the following:

    {}.extend($myCols.map(col => [col.id, col]))

    The .map() method converts the array into an array of key/value pairs that is then passed to the .extend() method which will construct the object. Now, we can lookup the title for a cell by doing $myCols[cell.columnId].

    With the columns in an easier form, the next mapper iterates over the list of rows to convert the cells into objects with this expression:

    $myRows.map(row => { rowNumber: row.rowNumber }.extend(
    row.cells.map(cell => [$myCols[cell.columnId].title, cell.get('value')]))
)

    The idea here is basically the same, the .map() call for the cells produces a key/value pair that is passed to .extend() to produce the object. The only real difference here is instead of calling .extend() on an empty object, we initialize it with the rowNumber property.

    (I’ve filed a feature request to add some functions to try to simplify this pattern a bit, arriving at these expressions is a bit round-a-bout…)

    • murphy-brian's avatar
      murphy-brian
      New Contributor III
      7 years ago

      Nice!
      Both solutions worked very well and gave me some great examples of using the script snap and the .extend method.

      Thanks to you both and happy new year!

    • murphy-brian's avatar
      murphy-brian
      New Contributor III
      7 years ago

      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's avatar
    Ajay_Chawda
    Contributor
    7 years ago

    in terms of execution time as compared to mapper snap.
    if same functionality can be achieved using mapper than it would be faster what i think.
    but for few use cases we have to go for script snap.