I have a JSON that's arriving from a REST data source in the format outlined below.
For the pupose of this exercise I'm looking to get all records of features.attributes to a table. I have, at the moment, a GET query that is successfully returning the JSON from the service. I then have a transformer that is referencing the query using the following:
return {{query1.rawData.features}}
The above returns everything, including a list of fields, so my table has fields, then features with attributes and geometry per feature. I can discard geometry and the list of fields here, and just keep attributes. The unfortunate thing is that every instance of a feature is feature.attributes.column_name. Attributes isn't a unique identifier, so might need to be addressed as an array? I don't know how to do this, and the number of items in the JSON can be highly variable.
How can I get each instance of attributes into a table with the columns as listed inside each atributes tag (they're all the same)?
Incoming JSON
{
"objectIdFieldName" : "OBJECTID",
"uniqueIdField" :
{
"name" : "OBJECTID",
"isSystemMaintained" : true
},
"globalIdFieldName" : "",
"geometryType" : "esriGeometryPoint",
"spatialReference" : {
"wkid" : 102100,
"latestWkid" : 3857
},
"fields" : [
{
"name" : "OBJECTID",
"type" : "esriFieldTypeOID",
"alias" : "OBJECTID",
"sqlType" : "sqlTypeOther",
"domain" : null,
"defaultValue" : null
},
{
"name" : "Master_Incident_Number",
"type" : "esriFieldTypeString",
"alias" : "Master_Incident_Number",
"sqlType" : "sqlTypeOther",
"length" : 200,
"domain" : null,
"defaultValue" : null
},
{
"name" : "Response_Date",
"type" : "esriFieldTypeDate",
"alias" : "Response_Date",
"sqlType" : "sqlTypeOther",
"length" : 8,
"domain" : null,
"defaultValue" : null
},
{
"name" : "LastUpdate",
"type" : "esriFieldTypeDate",
"alias" : "LastUpdate",
"sqlType" : "sqlTypeOther",
"length" : 8,
"domain" : null,
"defaultValue" : null
},
{
"name" : "CurrentStatus",
"type" : "esriFieldTypeString",
"alias" : "CurrentStatus",
"sqlType" : "sqlTypeOther",
"length" : 200,
"domain" : null,
"defaultValue" : null
},
{
"name" : "Location",
"type" : "esriFieldTypeString",
"alias" : "Location",
"sqlType" : "sqlTypeOther",
"length" : 200,
"domain" : null,
"defaultValue" : null
},
{
"name" : "Jurisdiction",
"type" : "esriFieldTypeString",
"alias" : "Jurisdiction",
"sqlType" : "sqlTypeOther",
"length" : 200,
"domain" : null,
"defaultValue" : null
},
{
"name" : "Latitude",
"type" : "esriFieldTypeDouble",
"alias" : "Latitude",
"sqlType" : "sqlTypeOther",
"domain" : null,
"defaultValue" : null
},
{
"name" : "Longitude",
"type" : "esriFieldTypeDouble",
"alias" : "Longitude",
"sqlType" : "sqlTypeOther",
"domain" : null,
"defaultValue" : null
},
{
"name" : "Locality",
"type" : "esriFieldTypeString",
"alias" : "Locality",
"sqlType" : "sqlTypeOther",
"length" : 256,
"domain" : null,
"defaultValue" : null
}
],
"features" : [
{
"attributes" : {
"OBJECTID" : 1,
"Master_Incident_Number" : "QF5S-22-034211",
"Response_Date" : 1648503591000,
"LastUpdate" : 1648592275420,
"CurrentStatus" : "Going",
"Location" : "Franklin St",
"Jurisdiction" : "5S Brisbane Region (South)",
"Latitude" : -27.546863,
"Longitude" : 153.006953,
"Locality" : "ROCKLEA"
},
"geometry" :
{
"x" : 17032656.095790341,
"y" : -3191962.7734574936
}
},
{
"attributes" : {
"OBJECTID" : 2,
"Master_Incident_Number" : "QF2-22-034410",
"Response_Date" : 1648535694000,
"LastUpdate" : 1648543690920,
"CurrentStatus" : "Patrolled",
"Location" : "KUNWARARA RD & RASPBERRY CREEK RD",
"Jurisdiction" : "2 Central Region",
"Latitude" : -23.027072,
"Longitude" : 150.26165,
"Locality" : "KUNWARARA"
},
"geometry" :
{
"x" : 16727050.363757096,
"y" : -2635292.8665138148
}
},
{
"attributes" : {
"OBJECTID" : 3,
"Master_Incident_Number" : "QF3-22-034596",
"Response_Date" : 1648596652000,
"LastUpdate" : 1648596828550,
"CurrentStatus" : "Going",
"Location" : "Surat Developmental Rd",
"Jurisdiction" : "3 South Western Region",
"Latitude" : -27.287846,
"Longitude" : 150.591122,
"Locality" : "GORANBA"
},
"geometry" :
{
"x" : 16763727.019027738,
"y" : -3159480.5616612332
}
},
{
"attributes" : {
"OBJECTID" : 5,
"Master_Incident_Number" : "QF2-22-034645",
"Response_Date" : 1648602545430,
"LastUpdate" : 1648606325033,
"CurrentStatus" : "Going",
"Location" : "Sandy Creek Rd",
"Jurisdiction" : "2 Central Region",
"Latitude" : -23.532428,
"Longitude" : 150.261996,
"Locality" : "BUSHLEY"
},
"geometry" :
{
"x" : 16727088.880300911,
"y" : -2696535.0529009691
}
},
{
"attributes" : {
"OBJECTID" : 8,
"Master_Incident_Number" : "QF3-22-034662",
"Response_Date" : 1648606479000,
"LastUpdate" : 1648607899347,
"CurrentStatus" : "Going",
"Location" : "CHINCHILLA KOGAN RD & CHINCHILLA SIXTEEN MILE RD",
"Jurisdiction" : "3 South Western Region",
"Latitude" : -26.885812,
"Longitude" : 150.642185,
"Locality" : "HOPELAND"
},
"geometry" :
{
"x" : 16769411.326186117,
"y" : -3109212.696569501
}
},
{
"attributes" : {
"OBJECTID" : 10,
"Master_Incident_Number" : "QF6-22-034661",
"Response_Date" : 1648606487000,
"LastUpdate" : 1648607560000,
"CurrentStatus" : "Going",
"Location" : "QUALLY RD",
"Jurisdiction" : "6 South Eastern Region",
"Latitude" : -27.466326,
"Longitude" : 152.385488,
"Locality" : "LOCKYER WATERS"
},
"geometry" :
{
"x" : 16963474.9284445,
"y" : -3181854.8064707648
}
}
]
}
Edit:
Trying to use the following, without much luck. Flatten or Filter Nested JSON - #4 by ben
let obj = query1.data
let result = []
obj.features.map( i => {
i.attributes.map( j => {
let row = {}
row.Master_Incident_Number = i.Master_Incident_Number;
row.CurrentStatus = j.CurrentStatus;
row.Location = j.Location;
result.push(row)
})
})
return result;
The error being returned is
message:"i.attributes.map is not a function"
Edit 2:
Also, calling .data.features to remove one level of nesting:
let obj = query1.data.features
let result = []
obj.attributes.map( i => {
let row = {}
row.Master_Incident_Number = i.Master_Incident_Number;
row.CurrentStatus = i.CurrentStatus;
row.Location = i.Location;
result.push(row)
})
return result;
Error
message:"Cannot read properties of undefined (reading 'map')"
Edit 3: Attmepting to use SQL to parse JSON
Query:
SELECT source->'Master_Incident_Number' as Master_Incident_Number, 'CurrentStatus' as CurrentStatus, 'Location' as Location from {{query1.data.features}}
Error:
message:"Parsing halted while starting to recover from another error."
