1 Correct answer
BACFL wrote
How do I parse the JSON data and then what type of query do I use to the database so that the zip codes in my results are then displayed on the webpage ordered by distance from the initial zip code? In this instance I queried 5 miles from 34242.
For ease, let's presume my database has two columns, "zipcode" and "make" of the vehicle I want to display,
i.e.
Ford 34242
Nissan 34233
Mazda 34236
Here is the sample JSON for a 5 mile radius of 34242:
{
"zip_codes": [
...
]
}
One suitable fu
...
26
Replies
Ok, let us start by assuming the zipCodeJSON is given. You can convert this JSON string to a ColdFusion object in which arrays contain the data. To do so, use the function deserializeJSON.
What then do you know about the resulting ColdFusion object? The JSON contains a hint.
In ColdFusion we know that {} stands for struct and [] for array. Consider, for example, the following JSON
zipCodeJSON='{
"zip_codes": [
{
"zip_code": "34238",
"distance": 4.647,
"city": "Sarasota",
"state": "FL"
},
{
"zip_code": "34242",
"distance": 0,
"city": "Sarasota",
"state": "FL"
}
]
}';
It has the structure,
{"zip_codes": [ {}, {} ] }
So it represents a struct containing an array called zip_codes. The array contains two structs which hold the data. That is what you will obtain when you deserialize the JSON:
zipCodeStructure=deserializeJSON(zipCodeJSON);
//writedump(zipCodeStructure);
In fact, you will see this when you dump the structure:
I shall now loop through the array elements, storing each as a row in a query.
zipCodeStructure=deserializeJSON(zipCodeJSON);
//writedump(zipCodeStructure);
// Define new, empty query
zipCodeQuery=queryNew("id,jsonName,city,distance,state,zipCode","Integer,Varchar,Varchar,Decimal,Varchar,Integer");
// Add each element of the zip_codes array as a row of the query
for (row=1; row lte arrayLen(zipCodeStructure.zip_codes); row=row+1) {
// I have included row number and "test" to each row.
// "Test" is just an arbitrary name. You might need a name to distinguish between JSONs
rowdata=[row,"test",zipCodeStructure.zip_codes[row].city,zipCodeStructure.zip_codes[row].distance,zipCodeStructure.zip_codes[row].state,zipCodeStructure.zip_codes[row].zip_code];
queryAddRow(zipCodeQuery,rowdata)
}
// Sort by distance (assuming you're on CF2016 or CF2018)
querySort(zipCodeQuery,function(obj1,obj2){
return compare(obj1.distance,obj2.distance);
});
// Sort by distance (assuming you're on CF 11 or older)
//zipCodeQuery.sort(zipCodeQuery.findColumn("distance"),true);
//writedump(zipCodeQuery);
AdChoices