I have poured over similar questions as this on SO and Google for 2 days. Here is the document
{
"rows" : [
{
"company" : "1 Gables Inn Bed & Breakfast",
"address" : " 318 Quapaw Avenue Hot Springs",
"a_url" : " AR 71901 Phone: 501-623-7576 ",
"json" : {
"results" : [
{
"address_components" : [
{
"long_name" : "318",
"short_name" : "318",
"types" : [ "street_number" ]
},
{
"long_name" : "Quapaw Avenue",
"short_name" : "Quapaw Ave",
"types" : [ "route" ]
},
{
"long_name" : "Hot Springs",
"short_name" : "Hot Springs",
"types" : [ "locality", "political" ]
},
{
"long_name" : "Hot Springs",
"short_name" : "Hot Springs",
"types" : [ "administrative_area_level_3", "political" ]
},
{
"long_name" : "Garland County",
"short_name" : "Garland County",
"types" : [ "administrative_area_level_2", "political" ]
},
{
"long_name" : "Arkansas",
"short_name" : "AR",
"types" : [ "administrative_area_level_1", "political" ]
},
{
"long_name" : "United States",
"short_name" : "US",
"types" : [ "country", "political" ]
},
{
"long_name" : "71901",
"short_name" : "71901",
"types" : [ "postal_code" ]
}
],
"formatted_address" : "318 Quapaw Avenue, Hot Springs, AR 71901, USA",
"geometry" : {
"bounds" : {
"northeast" : {
"lat" : 34.508227,
"lng" : -93.05744779999999
},
"southwest" : {
"lat" : 34.50822,
"lng" : -93.05746420000001
}
},
"location" : {
"lat" : 34.508227,
"lng" : -93.05746420000001
},
"location_type" : "RANGE_INTERPOLATED",
"viewport" : {
"northeast" : {
"lat" : 34.5095724802915,
"lng" : -93.05610701970851
},
"southwest" : {
"lat" : 34.5068745197085,
"lng" : -93.05880498029151
}
}
},
"types" : [ "street_address" ]
}
],
"status" : "OK"
}
},
{
"company" : "1000 Acres Retreat House",
"address" : " 122 Russell Street Hot Springs ",
"a_url" : " AR 71901 501-318-4117 Website ",
"json" : {
"results" : [
{
"address_components" : [
{
"long_name" : "122",
"short_name" : "122",
"types" : [ "street_number" ]
},
{
"long_name" : "Russell Street",
"short_name" : "Russell St",
"types" : [ "route" ]
},
{
"long_name" : "Hot Springs",
"short_name" : "Hot Springs",
"types" : [ "locality", "political" ]
},
{
"long_name" : "Hot Springs",
"short_name" : "Hot Springs",
"types" : [ "administrative_area_level_3", "political" ]
},
{
"long_name" : "Garland County",
"short_name" : "Garland County",
"types" : [ "administrative_area_level_2", "political" ]
},
{
"long_name" : "Arkansas",
"short_name" : "AR",
"types" : [ "administrative_area_level_1", "political" ]
},
{
"long_name" : "United States",
"short_name" : "US",
"types" : [ "country", "political" ]
},
{
"long_name" : "71901",
"short_name" : "71901",
"types" : [ "postal_code" ]
}
],
"formatted_address" : "122 Russell Street, Hot Springs, AR 71901, USA",
"geometry" : {
"location" : {
"lat" : 34.5313788,
"lng" : -93.05564799999999
},
"location_type" : "ROOFTOP",
"viewport" : {
"northeast" : {
"lat" : 34.5327277802915,
"lng" : -93.05429901970848
},
"southwest" : {
"lat" : 34.5300298197085,
"lng" : -93.0569969802915
}
}
},
"types" : [ "street_address" ]
}
],
"status" : "OK"
}
}
]
}
I case you are wondering, these rows are from Google geocoding result
What I am trying to do is locate and extract the "postal_code" for each address. The position of the "postal_code" can vary from request to request, so we cannot rely on a fixed, expected position in the array. Instead we must identify the "types" equal to "postal_code", then extract the extract the corresponding "long_name". Ultimately that "postal_code" is going back into MySQL, so I guess that objective here is to "flatten" the document.
In case it helps anyone, here is what a record looks like in Mongodb:
Each record is in its own array row, which seems to complicate the query, in that I have tried variations on the following:
db.AR.find({ 'results.address_components.types': { $in: ["postal_code"] } }).toArray()
db.AR.find({ 'rows.results.address_components.types': { $in: ["postal_code"] } }).toArray()
...and cannot seem to get any results reutnred. Nothing, Zilch. Nada.
btw, this is my first effor with MongoDB since it seems to be the tools most suited to this type of problem. I have also tried the MySQL "json_extract" UDF functions, but I can't seem to make any headway with that, either. So I guess what I am syaing is that if I am making this more difficult than it needs to be--and someone has a better approach--I am open to suggestions.
Shameless
