Parse JSON in Google Refine

Question

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"]

Answer 1

The field named politics is an array, which you return with:

value.parseJson()[0]["politics"]

One element of that array is associated with the county (it's the one whose friendly_type field is "county"). So you need to filter the politics field to find the one whose friendly_type is county, like this:

filter(value.parseJson()[0]["politics"], item, item["friendly_type"]=="county")

That returns an array with one element. You want to get the name field out of that one element, so you need to extract the name of the zeroth array element, making your complete expression:

filter(value.parseJson()[0]["politics"], item, item["friendly_type"]=="county")[0]["name"]