Multi-dimensional arrays and combining data between two arrays

[SNNoob]

Hello!

I have a Flow Designer Flow where I get a JSON response from a third party application that is then needed to use for updating specific records in ServiceNow.

Response looks something like this:

{
"columns": [
"ColumnTitle1",
"ColumnTitle2",
"ColumnTitle3"
],
"reportTemplate": "MyReportData",
"dataGrid": [
[
"Row1Value1",
"Row1Value2",
"Row1Value3"
],[
"Row2Value1",
"Row2Value2",
"Row3Value3"
]
],
"hasMore": true,
"cursor": "string"
}

In this example, columns refer to fields that can be anything. dataGrid contains all the particular rows from the data source in a multidimensional array. There can be 1 to n amount of rows in each response.

Data matching would be like this:

ColumnTitle1: Row1Value1

ColumnTitle2: Row1Value2

ColumnTitle1: Row2Value1

ColumnTitle2: Row2Value2 etc.

Row values also contain the coalesce value, which would be used to match the data to the correct record in ServiceNow

So, how would I start to parse this kind of stuff together into something that could be used to actually update something? I would use JSON Parser step, but it does not support multidimensional arrays and I have limited experience with array scripting proper.

If something is unclear, do ask and I'll try explain better!

[Hitoshi Ozawa]

My bad. Since fields are dynamic, won't be able to use update set.

I haven't tested the script but something like below should update records using the 1st field in the record as a key.

var columnNames = [];
var columns = data.columns;
for (var i = 0; i < columns.length; i++) {
    columnNames.push(columns[i]);
}
var dataGrid = data.dataGrid;

for (var j = 0; j < dataGrid.length; j++) {
    var grid = dataGrid[j];
    if (columnNames.length != grid.length && columnNames.length > 0) {
        gs.error('number of column names does not match number of element in data grid:' + grid);
    } else {
        var grTable = new GlideRecord('<table name of import set table>');
        var key = columnNames[0];
        var keyValue = grid[0];
        grTable.addQuery(key, keyValue);
        grTable.query();
        if (grTable.next()) {
            for (var k = 1; k < grid.length; k++) {
                grTable.setValue(columnNames[k], grid[k]);
            }
            grTable.update();
        }
    }
}

[SNNoob]

Hello @Hitoshi Ozawa 

I used yours and Pedro's code to create these kinds of arrays:

{
  "ColumnTitle1":[  
    "Row1Value1",
    "Row2Value1"
  ],
  "ColumnTitle2":[  
    "Row1Value2",
    "Row2Value2"
  ],
  "ColumnTitle3":[  
    "Row1Value3",
    "Row2Value3"
  ]
}

//What I would need is the following (then I can use for each loops in Flow Designer)

//Each array containing the relevant record values, instead of them being separated. So all Row1 values in a single array, Row2 values in another, etc. then the arrays would be record specific


// I did face some looping issues while trying to update with your code, also trying that out in pararell with some changes, will keep you updated

[Hitoshi Ozawa]

@SNNoob Forgot to tell. "For Each Item in" step is not able to loop through an array object. So it would be necessary to process the array in Action and not in Flow.

Also, Update Record step would require a fix table field name. If the column name are dynamic, it would require a script.

>columns refer to fields that can be anything

 

[SNNoob]

Hi again @Hitoshi Ozawa ,

Thanks again. I think it is better to clarify again the source JSON and what I want to achieve with it.

The JSON I get is structured like this:

{
"columns": [
"ID",
"Year",
"Application"
],
"reportTemplate": "mysource resport",
"dataGrid": [
[
"23552gnk35nk3nkn5",
"2022",
"Windows"
],
[
"5747347kkjetjojoe",
"2010",
"Linux"
],
"hasMore": true,
"cursor": "mycursor"
}

The colums are static and they represent fields in the source application.

In dataGrid, the arrays within it represent a singular row (or record) in the source data. The order that the rows are within these arrays are the same as in columns.

For example, in this data 2 records are received. First one has ID of 23552gnk35nk3nkn5, second one has ID of 5747347kkjetjojoe. And so forth with other colums and data.

What I want to do is to use a value within these arrays (like ID) to find particular records in a table in ServiceNow where there is a matching ID and then update fields values, like year and application from the same array.

Hopefully this clarifies a bit. Sorry for misunderstandings that may have arisen 🙂