JSON parsing in Google Refine

Question

I am trying to pull certain elements from the results of the DataControlit Coordics2politics API using Google Refine.

Here is an example of 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 slightly 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 a way to sort JSON or phrases of my syntax so that I can find the county anyway?

Update

Here's a magical GREL that allowed me to find elements in a JSON string by name, and not just by position:

 filter(value.parseJson()[0]["politics"], item, item["type"]=="admin6")[0]["name"] 

Answer 1

The field named politics is an array that you return with:

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

One element of this array is associated with the county (this is one whose friendly_type field is "county"). Therefore, you need to filter out the politics field to find one whose friendly_type is a county, for example:

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

Returns an array with one element. You want to get the name field from this one element, so you need to extract the name element of the zero array, making your full expression:

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