parse/seperate data from string

Report · Jan 12, 2010

I have a string that is returned from Google GeoCoding:

{ "name": "193 Farrow Hill Road,Davisville,WV", "Status": { "code": 200, "request": "geocode" }, "Placemark": [ { "id": "p1", "address": "Davisville, WV, USA", "AddressDetails": { "Accuracy" : 4, "Country" : { "AdministrativeArea" : { "AdministrativeAreaName" : "WV", "SubAdministrativeArea" : { "Locality" : { "LocalityName" : "Davisville" }, "SubAdministrativeAreaName" : "Wood" } }, "CountryName" : "USA", "CountryNameCode" : "US" } }, "ExtendedData": { "LatLonBox": { "north": 39.2357655, "south": 39.1665921, "east": -81.4344257, "west": -81.5624851 } }, "Point": { "coordinates": [ -81.4984554, 39.2011873, 0 ] } } ] }

I want to be able to parse this data into variables that I can use and update a table.

I currently use ListGetAt() function and try to find common delimiters to narrow down what I am trying to parse, but this processes does not always return the results I am needing.

Does anyone know of any faster more robust way of parsing this string down to variables? And, it could be that I am not using the ListGetAt() to its full potential as well....

What I need most out of this string is in bold below...you'll notice different string lengths depending on if it recognizes the street address or not.

This is the string returned if it DOES NOT recognize the street address:

{ "name": "193 Farrow Hill Road,Davisville,WV", "Status": { "code": 200, "request": "geocode" }, "Placemark": [ { "id": "p1", "address": "Davisville, WV, USA", "AddressDetails": { "Accuracy" : 4, "Country" : { "AdministrativeArea" : { "AdministrativeAreaName" : "WV", "SubAdministrativeArea" : { "Locality" : { "LocalityName" : "Davisville" }, "SubAdministrativeAreaName" : "Wood" } }, "CountryName" : "USA", "CountryNameCode" : "US" } }, "ExtendedData": { "LatLonBox": { "north": 39.2357655, "south": 39.1665921, "east": -81.4344257, "west": -81.5624851 } }, "Point": { "coordinates": [ -81.4984554, 39.2011873, 0 ] } } ] }

This is the string returned if it DOES recognize the street address:

{ "name": "W7499 So. Mound Rd,Neillsville,WI", "Status": { "code": 200, "request": "geocode" }, "Placemark": [ { "id": "p1", "address": "S Mound Rd, Neillsville, WI 54456, USA", "AddressDetails": { "Accuracy" : 6, "Country" : { "AdministrativeArea" : { "AdministrativeAreaName" : "WI", "SubAdministrativeArea" : { "Locality" : { "LocalityName" : "Neillsville", "PostalCode" : { "PostalCodeNumber" : "54456" }, "Thoroughfare" : { "ThoroughfareName" : "S Mound Rd" } }, "SubAdministrativeAreaName" : "Clark" } }, "CountryName" : "USA", "CountryNameCode" : "US" } }, "ExtendedData": { "LatLonBox": { "north": 44.5921279, "south": 44.5858326, "east": -90.5981846, "west": -90.6802503 } }, "Point": { "coordinates": [ -90.6394276, 44.5889072, 0 ] } }, { "id": "p2", "address": "S Mound Rd, Neillsville, WI 54456, USA", "AddressDetails": { "Accuracy" : 6, "Country" : { "AdministrativeArea" : { "AdministrativeAreaName" : "WI", "SubAdministrativeArea" : { "Locality" : { "LocalityName" : "Neillsville", "PostalCode" : { "PostalCodeNumber" : "54456" }, "Thoroughfare" : { "ThoroughfareName" : "S Mound Rd" } }, "SubAdministrativeAreaName" : "Clark" } }, "CountryName" : "USA", "CountryNameCode" : "US" } }, "ExtendedData": { "LatLonBox": { "north": 44.5920678, "south": 44.5857725, "east": -90.6802503, "west": -90.7004168 } }, "Point": { "coordinates": [ -90.6899796, 44.5889209, 0 ] } } ] }

Report · Jan 12, 2010

That string format looks like JSON. Try running it through DeserializeJSON().

Report · Jan 12, 2010

That is indeed JSON, as Jochem says. You can pick out data using structs and arrays. You will know which structure or array functionality to use after doing something like this

<cfsavecontent variable="myJSON1">
{ "name": "193 Farrow Hill Road,Davisville,WV", "Status": { "code": 200, "request": "geocode" }, "Placemark": [ { "id": "p1", "address": "Davisville, WV, USA", "AddressDetails": { "Accuracy" : 4, "Country" : { "AdministrativeArea" : { "AdministrativeAreaName" : "WV", "SubAdministrativeArea" : { "Locality" : { "LocalityName" : "Davisville" }, "SubAdministrativeAreaName" : "Wood" } }, "CountryName" : "USA", "CountryNameCode" : "US" } }, "ExtendedData": { "LatLonBox": { "north": 39.2357655, "south": 39.1665921, "east": -81.4344257, "west": -81.5624851 } }, "Point": { "coordinates": [ -81.4984554, 39.2011873, 0 ] } } ] }
</cfsavecontent>

<cfsavecontent variable="myJSON2">
{ "name": "W7499 So. Mound Rd,Neillsville,WI", "Status": { "code": 200, "request": "geocode" }, "Placemark": [ { "id": "p1", "address": "S Mound Rd, Neillsville, WI 54456, USA", "AddressDetails": { "Accuracy" : 6, "Country" : { "AdministrativeArea" : { "AdministrativeAreaName" : "WI", "SubAdministrativeArea" : { "Locality" : { "LocalityName" : "Neillsville", "PostalCode" : { "PostalCodeNumber" : "54456" }, "Thoroughfare" : { "ThoroughfareName" : "S Mound Rd" } }, "SubAdministrativeAreaName" : "Clark" } }, "CountryName" : "USA", "CountryNameCode" : "US" } }, "ExtendedData": { "LatLonBox": { "north": 44.5921279, "south": 44.5858326, "east": -90.5981846, "west": -90.6802503 } }, "Point": { "coordinates": [ -90.6394276, 44.5889072, 0 ] } }, { "id": "p2", "address": "S Mound Rd, Neillsville, WI 54456, USA", "AddressDetails": { "Accuracy" : 6, "Country" : { "AdministrativeArea" : { "AdministrativeAreaName" : "WI", "SubAdministrativeArea" : { "Locality" : { "LocalityName" : "Neillsville", "PostalCode" : { "PostalCodeNumber" : "54456" }, "Thoroughfare" : { "ThoroughfareName" : "S Mound Rd" } }, "SubAdministrativeAreaName" : "Clark" } }, "CountryName" : "USA", "CountryNameCode" : "US" } }, "ExtendedData": { "LatLonBox": { "north": 44.5920678, "south": 44.5857725, "east": -90.6802503, "west": -90.7004168 } }, "Point": { "coordinates": [ -90.6899796, 44.5889209, 0 ] } } ] }
</cfsavecontent>