unable to parse JSON

40 views
Skip to first unread message

Sebastian Lipp

unread,
Sep 1, 2017, 6:07:20 PM9/1/17
to openr...@googlegroups.com
Hello group,

i want to filter a specific value from a JSON-filled column and write it
to a new one but I cant't figure out how to do that. I would really
encourage any help from this list as this problem already took two
evenings from me without any progress.

I'm adding a coloum based on another one fetching geodata via
Mapquest. No problem here. Results look like the attached long.json (see
[1]).

I'd like to filter that JSON's fields "lat" and "lng" into new
columns. But any filtering I tried only works at the highest level of
the JSON. For example value.parseJson()["lng"] returns null while
value.parseJson()["results"] returns the subset you'd expect (attached
as subset.json).

Filtering again on that subset (eg by value.parseJson()["latLng"])
returns null and I'm out of ideas.

How do I get those values via GREL?

Best,
Sebastian

[1]:
<https://developer.mapquest.com/documentation/geocoding-api/address/get/>

long.json
subset.json

ettor...@gmail.com

unread,
Sep 2, 2017, 3:41:33 AM9/2/17
to OpenRefine
Hi Sebastian, 

it's normal that value.parseJson()["lng"] does not return anything, because this path is not complete. To know the exact path of an element in a Json, I recommend PickyJson

By looking at your file "long.json", we see that the element "lat" follows the following path: results["0"].locations["0"].displayLatLng.lat



This is the one you should indicate after value.parseJson() (attention, PickYJson adds quotes around the index numbers. You have to delete them)) :

value.parseJson().results[0].locations[0].displayLatLng.lat

If you want to extract several latitudes or longitudes in a single Json, you will need to use one or two forEach loop.

Hope this helps.

Sebastian Lipp

unread,
Sep 2, 2017, 12:26:29 PM9/2/17
to OpenRefine
That dit it.

Thank you!

ettor...@gmail.com writes:
> Hi Sebastian,
>
> it's normal that value.parseJson()["lng"] does not return anything, because
> this path is not complete. To know the exact path of an element in a Json,
> I recommend PickyJson <http://pickyjson.com/>.
>
> By looking at your file "long.json", we see that the element "lat" follows
> the following path: results["0"].locations["0"].displayLatLng.lat
>
> <https://lh3.googleusercontent.com/-KM19ODL_wB0/WapgW4olcVI/AAAAAAAAUXI/pfyRoOlOh0kjqCR_YDlWOlaUtdKpESPLQCLcBGAs/s1600/screenshot-www.mapquestapi.com-2017-09-02-09-27-41.jpeg>
> --
> You received this message because you are subscribed to the Google Groups "OpenRefine" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages