在Google Refine中解析JSON [英] Parse JSON in Google Refine

问题描述

我正在尝试使用Google Refine从Data Science Toolkit坐标2政治API的结果中提取特定元素.

I'm trying to pull out specific elements from results from the Data Science Toolkit coordinates2politics API, using Google Refine.

这是示例单元格#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"}}]

我使用此GREL语法在此列的基础上添加了一个列，以拔出该县Dane:

I added a column based on this column using this GREL syntax to pull out the county, Dane:

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

但是当我进入Sample Cell#2时，语法不再起作用，因为JSON结果略有不同:

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

是否可以通过某种方式对JSON或短语进行语法排序，以便在两种情况下都可以找到县?

Is there some way to sort the JSON or phrase my syntax so that I can find the county in either case?

这是神奇的GREL，它使我能够按名称而不是位置查找JSON字符串中的元素:

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

推荐答案

名为politics的字段是一个数组，您可以通过以下方式返回:

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

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

该数组中的一个元素与县相关联(这是其friendly_type字段为"county"的元素).因此，您需要过滤politics字段以找到其friendly_type是县的字段，如下所示:

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

这将返回一个包含一个元素的数组.您想从该元素中获取name字段，因此您需要提取第零个数组元素的name，使表达式完整:

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

这篇关于在Google Refine中解析JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！