I'm trying to pull out specific elements from results from the Data Science Toolkit coordinates2politics API, using Google Refine.
Here is sample cell #1:
[{"politics":[
{"type":"admin2","friendly_type":"country","code":"usa","name":"United States"},
{"type":"admin6","friendly_type":"county","code":"55_025","name":"Dane"},
{"type":"constituency","friendly_type":"constituency","code":"55_02","name":"Second district, WI"},
{"type":"admin5","friendly_type":"city","code":"55_48000","name":"Madison"},
{"type":"admin5","friendly_type":"city","code":"55_53675","name":"Monona"},
{"type":"admin4","friendly_type":"state","code":"us55","name":"Wisconsin"},
{"type":"neighborhood","friendly_type":"neighborhood","code":"Eastmorland|Madison|WI","name":"Eastmorland"}
],"location":{"longitude":"-89.3259404","latitude":"43.0859191"}}]
I added a column based on this column using this GREL syntax to pull out the county, Dane:
value.parseJson()[0]["politics"][1]["name"]
But when I got to Sample Cell #2, the syntax no longer works because the JSON result is a little different:
[{"politics":[
{"type":"admin2","friendly_type":"country","code":"usa","name":"United States"},
{"type":"constituency","friendly_type":"constituency","code":"55_05","name":"Fifth district, WI"},
{"type":"admin4","friendly_type":"state","code":"us55","name":"Wisconsin"},
{"type":"admin6","friendly_type":"county","code":"55_079","name":"Milwaukee"},
{"type":"admin5","friendly_type":"city","code":"55_84675","name":"Wauwatosa"},
{"type":"constituency","friendly_type":"constituency","code":"55_04","name":"Fourth district, WI"}
],"location":{"longitude":"-88.0075875","latitude":"43.0494572"}}]
Is there some way to sort the JSON or phrase my syntax so that I can find the county in either case?
Update
Here's the magic GREL that allowed me to find elements in the JSON string by name, not just position:
filter(value.parseJson()[0]["politics"], item, item["type"]=="admin6")[0]["name"]