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 S…

Accepted Answer

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

Parse JSON in Google Refine

Solution

Problem