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!

[Pedro Grilo1]

Hi,

Not sure it helps but you can get all the row values in separate columns. Then you can loop on the first column and use the same index for all others to have a concept of row. The example below would give you one object where the keys are the column titles. Each key will be an array of values per each row:

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

var columns = [];
var resultObj = {};
aux.columns.forEach(function(column) {
	resultObj[column] = [];
  columns.push(column);
});

aux.dataGrid.forEach(function(rowValue) {
  for(var i=0; i<rowValue.length; i++) {
   	var columnName =  columns[i];
    resultObj[columnName].push(rowValue[i]);
  }
});

gs.print(resultObj);

I hope it helps!

Pedro

[SNNoob]

Thanks Pedro! I posted some updates below under replies to Hiroshi, if you are looking for updates on this item.

[Hitoshi Ozawa]

Hi SNNoob,

I think the question after this will be how to create or update the record using Flow Designer step where fields to be updated are dynamic.

AFAIK, I don't think this is possible.

That is, it's probably necessary to insert the records using Script step in Action. Since there is a coalesce column that is currently not specified in the data, data can be imported into import set table.

https://docs.servicenow.com/en-US/bundle/sandiego-platform-administration/page/administer/import-set...

var data = {
"columns": [
"ColumnTitle1",
"ColumnTitle2",
"ColumnTitle3"
],
"reportTemplate": "MyReportData",
"dataGrid": [
[
"Row1Value1",
"Row1Value2",
"Row1Value3"
],[
"Row2Value1",
"Row2Value2",
"Row3Value3"
]
],
"hasMore": true,
"cursor": "string"
}
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) {
    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>');
    grTable.initialize();
    for (var k=0; k < grid.length; k++) {
      grTable.setValue(columnNames[k], grid[k]);
    }
    grTable.insert();
  }
}

[SNNoob]

Hi @Hitoshi Ozawa ,

Thanks for this, this is in the right direction for what I'm looking for! I can confirm that with this, it is possible to trigger a transform from within a Flow, where the JSON response is received in a previous step.

Now it is about mapping logic. As you mentioned, there is a coalesce value here (in my use case, this is always about updating records, never inserting new ones). This is present in all rows in the value position (so if we use the my example, let's say that Row1Value1 & Row2Value1 would be the coalesce values, with each Row representing a unique record and each Value a unique field value for that row.).

While I have done plenty of Transform maps in the past, I have never done it like this via scripting from JSON source that includes parsing. Any ideas on how should field mapping handled in this case?