GET Request with JSON Parse

I am trying to parse telematics data, and am very new to Knime, but not new to ETL tools.

The data from my REST API Get is formatted like

{
“data”: [
{
“attributes”: [
{
“id”: “d64e2b41-f233-401c-83e6-786454343578”,
“name”: “XXX AASD”,
“stringValues”: [
“08.LF”
]
}
],
“cameraSerial”: “TRGD-VB9-S55”,
“externalIds”: {
“samsara.serial”: “GF9GFDAG13”,
“samsara.vin”: “1FTSSASDFBDR7804”
},
“gateway”: {
“serial”: “GAD-VPD-P4F”,
“model”: “VGFSNA”
},
“harshAccelerationSettingType”: “automatic”,
“id”: “28147434587833”,
“licensePlate”: “00481M1”,
“make”: “FORD”,
“model”: “E-350”,
“name”: “TRUCK”,
“notes”: “”,
“serial”: “GF9V334534C”,
“staticAssignedDriver”: {
“id”: “495143463”,
“name”: “Goeff Dunsworth”
}],
“pagination”: {
“endCursor”: “”,
“hasNextPage”: false
}
}

Using JSON to Table Knime creates a column for each data point, there is no grouping. So there is a Column
data.0.cameraSerial and then of course there is more than 1 vehicle so there will be a data.1.cameraSerial and data.2.cameraSerial columncs etc.

Not sure if my json to table is incorrect or if I should be using ungroup? but I’m unfamiliar with Ungroup as well,

thanks in advance,

I’m not a JSON expert so maybe others can weigh in. You need to ungroup. See if this workflow helps.
json demo.knwf (160.6 KB)

1 Like

thank you for the reply.

I used your example, and while wildly different, it helped me understand a bit about the syntax to use the JSONPath tool. I ended up getting what I needed from the rest request, thanks very much!

Glad I could help you.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.