Data Stream Action – Unable to Retrieve All Records from Nested Arrays in REST Response
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 hours ago - last edited 2 hours ago
Hello Experts,
I have created a Data Stream Action in Flow Designer for a REST integration and am able to successfully retrieve data. However, I am encountering an issue while parsing the response due to its structure.
The API returns data in a tabular format containing multiple tables (Table_0, Table_1, Table_2, and Table_3). The actual user information resides in Table_0, specifically within the Rows array (highlighted in Blue).
🔹Sample JSON Response
{
"Tables": [{
"TableName": "Table_0",
"Columns": [{
"ColumnName": "email",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "username",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "name",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "user_id",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "last_used",
"DataType": "DateTime",
"ColumnType": "datetime"
}],
"Rows": [
["Savyasachi.Nemani@example.com", "SNemani2", "Savyasachi Nemani", "6262878", "2026-04-08T06:36:54Z"],
["Scott.Crenshaw2@example.com", "SCrensh2", "Scott Crenshaw", "5859935", "2026-04-08T06:36:54Z"],
["", "SVC_PRD_RANANIX", "SVC_PRD_RANANIX@example.com", "20025198", "2026-04-08T06:36:54Z"],
["Carlos.Martinez576@example.com", "CMartin576", "Carlos Martinez", "11396655", "2026-04-08T06:36:54Z"],
["AmithaPrem.Pothureddy2@example.com", "APothur2", "AmithaPrem Pothureddy", "10394676", "2026-04-08T06:36:54Z"],
["Jeeten.Shrestha@example.com", "JShrest3", "Jeeten Shrestha", "4242429", "2026-04-08T06:36:54Z"]
]
}, {
"TableName": "Table_1",
"Columns": [{
"ColumnName": "Value",
"DataType": "String",
"ColumnType": "string"
}],
"Rows": [
["{\"Visualization\":null,\"Title\":null,\"XColumn\":null,\"Series\":null,\"YColumns\":null,\"AnomalyColumns\":null,\"XTitle\":null,\"YTitle\":null,\"XAxis\":null,\"YAxis\":null,\"Legend\":null,\"YSplit\":null,\"Accumulate\":false,\"IsQuerySorted\":false,\"Kind\":null,\"Ymin\":\"NaN\",\"Ymax\":\"NaN\",\"Xmin\":null,\"Xmax\":null}"]
]
}, {
"TableName": "Table_2",
"Columns": [{
"ColumnName": "Timestamp",
"DataType": "DateTime",
"ColumnType": "datetime"
}, {
"ColumnName": "Severity",
"DataType": "Int32",
"ColumnType": "int"
}, {
"ColumnName": "SeverityName",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "StatusCode",
"DataType": "Int32",
"ColumnType": "int"
}, {
"ColumnName": "StatusDescription",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "Count",
"DataType": "Int32",
"ColumnType": "int"
}, {
"ColumnName": "RequestId",
"DataType": "Guid",
"ColumnType": "guid"
}, {
"ColumnName": "ActivityId",
"DataType": "Guid",
"ColumnType": "guid"
}, {
"ColumnName": "SubActivityId",
"DataType": "Guid",
"ColumnType": "guid"
}, {
"ColumnName": "ClientActivityId",
"DataType": "String",
"ColumnType": "string"
}],
"Rows": [
["2026-04-08T10:00:06.4015472Z", 4, "Info", 0, "Query completed successfully", 2, "2450336c-573f-44c7-90fc-93b3c09c2e8e", "2450336c-573f-44c7-90fc-93b3c09c2e8e", "470bbed5-aa84-445f-b016-29014a337b3c", "unspecified;ea7ca795-280f-4f37-8708-6d7495690106"],
["2026-04-08T10:00:06.4438722Z", 6, "Stats", 0, "{\"QueryHash\":\"1f567da9e29772d\",\"ExecutionTime\":0.0595677,\"resource_usage\":{\"cache\":{\"shards\":{\"hot\":{\"hitbytes\":4770007,\"missbytes\":0,\"retrievebytes\":0},\"cold\":{\"hitbytes\":1201853,\"missbytes\":0,\"retrievebytes\":0},\"bypassbytes\":0}},\"cpu\":{\"user\":\"00:00:00.0156250\",\"kernel\":\"00:00:00\",\"total cpu\":\"00:00:00.0156250\",\"breakdown\":{\"query execution\":\"00:00:00.0156250\",\"query planning\":\"00:00:00\"}},\"memory\":{\"peak_per_node\":2163728},\"network\":{\"inter_cluster_total_bytes\":1660748,\"cross_cluster_total_bytes\":0}},\"input_dataset_statistics\":{\"extents\":{\"total\":302,\"scanned\":152,\"scanned_min_datetime\":\"2025-10-10T12:42:17.1331071Z\",\"scanned_max_datetime\":\"2026-04-08T06:43:29.5661540Z\"},\"rows\":{\"total\":14183572,\"scanned\":7102013},\"rowstores\":{\"scanned_rows\":0,\"scanned_values_size\":0},\"shards\":{\"queries_generic\":1,\"queries_specialized\":0}},\"dataset_statistics\":[{\"table_row_count\":8767,\"table_size\":686026}],\"cross_cluster_resource_usage\":{}}", 1, "2450336c-573f-44c7-90fc-93b3c09c2e8e", "2450336c-573f-44c7-90fc-93b3c09c2e8e", "470bbed5-aa84-445f-b016-29014a337b3c", "unspecified;ea7ca795-280f-4f37-8708-6d7495690106"]
]
}, {
"TableName": "Table_3",
"Columns": [{
"ColumnName": "Ordinal",
"DataType": "Int64",
"ColumnType": "long"
}, {
"ColumnName": "Kind",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "Name",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "Id",
"DataType": "String",
"ColumnType": "string"
}, {
"ColumnName": "PrettyName",
"DataType": "String",
"ColumnType": "string"
}],
"Rows": [
[0, "QueryResult", "PrimaryResult", "81c9ccb7-fee7-4a51-a723-6f4459b1c299", ""],
[1, "QueryProperties", "@ExtendedProperties", "f16456e0-2020-4e7e-ab7d-47508fa5eda0", ""],
[2, "QueryStatus", "QueryStatus", "00000000-0000-0000-0000-000000000000", ""]
]
}]
}
🔹Item Path Configuration
Current Item Path: $.Tables
Attempted Item Path: $.Tables[0].Rows
Issue: Using $.Tables[0].Rows results in an "Item Path is incorrect" error.
When I configure the Item Path as $.Tables, the Data Stream iterates through each table. As a result, only the first record from Table_0 is processed before the stream moves to the next table. Consequently, I receive only one user record instead of all records.
🔹Script Used in the Script Parser Step
(function parse(inputs, outputs) {
var record = JSON.parse(inputs.sourceItem);
var rowObj = {};
// Process only Table_0
if (record.TableName != "Table_0") {
return;
}
// Extract column names
var columns = record.Columns.map(function(col) {
return col.ColumnName;
});
var rows = record.Rows;
// Loop through each row
for (var j = 0; j < rows.length; j++) {
var row = rows[j];
rowObj = {}; // Reset object for each iteration
// Map column names to row values
for (var k = 0; k < columns.length; k++) {
rowObj[columns[k]] = row[k];
}
// Extract required fields
outputs.targetObject.user_id = rowObj.user_id;
outputs.targetObject.email = rowObj.email;
outputs.targetObject.last_activity_at = rowObj.last_used;
gs.info('UserId: ' + outputs.targetObject.user_id +
' | Email: ' + outputs.targetObject.email +
' | Last Used: ' + outputs.targetObject.last_activity_at);
}
})(inputs, outputs);🔹Issue Description
The response contains four tables, but only Table_0 holds the required user data.
Because the Item Path is $.Tables, the Data Stream iterates through each table.
During execution, only one record from Table_0 is retrieved before moving to the next table.
Consequently, only a single user record is processed.
🔹Expected Outcome
I would like the Data Stream Action to:
Process only Table_0.
Iterate through all records in the Rows array.
Map the following fields:
user_id
email
last_activity_at
🔹What I Have Tried
- Verified that the REST step returns the expected response.
- Used $.Tables as the Item Path, which works but retrieves all tables, including metadata.
- Attempted to filter only Table_0, but without success.
- Considered using a Scripted Item Path but am unsure of the correct implementation.
🔹Questions
Why does $.Tables[0].Rows return an "Item Path is incorrect" error?
Is it possible to configure the Data Stream Action to iterate only through Table_0?
Is there a recommended approach to flatten this response before parsing?
Should a Scripted Item Path be used instead? If so, what would be the recommended approach?
Any guidance or best practices would be greatly appreciated.
Thank you in advance for your support!
Best regards,
Sneha Karthik
