0

I've got a really stupid question but I can't seem to find any answer whatsoever. I have this JSON from an online weather API, the JSON looks like this:

{"coord":{"lon":4.4,"lat":51.22},"sys":{"type":1,"id":4839,"message":0.1065,"country":"Belgium","sunrise":1414218231,"sunset":1414254541},"weather":[{"id":701,"main":"Mist","description":"mist","icon":"50d"},{"id":310,"main":"Drizzle","description":"light intensity drizzle rain","icon":"09d"}],"base":"cmc stations","main":{"temp":286.3,"pressure":1019,"humidity":87,"temp_min":285.15,"temp_max":287.15},"wind":{"speed":3.1,"deg":210},"clouds":{"all":75},"dt":1414232888,"id":2803138,"name":"Antwerp","cod":200}

I want to use this JSON data in a datawarehouse tool Pentaho. I'm stuck though on how to get the right values.

Say I want to retrieve "country" for example, which is located inside "sys". How would I go about retrieving it using the dollar sign notation? I tried $.sys.country but that didn't work. I'm not sure how to translate this JSON structure to dollar sign notation.

Really hope someone can help, thanks in advance!

E. V. d. B.
  • 815
  • 2
  • 13
  • 30
  • Have you looked at the docs for the data warehouse you're using? This seems like something that a tutorial should explain. – John Zwinck Oct 25 '14 at 13:04
  • The JSONPath syntax I used was right al along, there was just a problem with my workflow that I solved using a tutorial! I've explained it below and gave my useful links too! Thanks anyways ;) – E. V. d. B. Oct 25 '14 at 21:34
  • Go to json.org and study the JSON syntax. It only takes 5-10 minutes to learn. Then, using whatever toolkit you have, parse the JSON into the kit's internal representation and carefully "disassemble" the JSON by "peeling" a layer at a time. Print your results at each step so you understand what's going on. – Hot Licks Oct 25 '14 at 21:41

1 Answers1

1

After some more surfing and googling I found the solution. I wasn't aware, but the specific data warehousing tool I use (Pentaho) uses JSONPath (http://goessner.net/articles/JsonPath/ - it's the official JSONPath website but Chrome gives me a Malware warning for some reason...) for getting data out of JSON. The syntax I used in my original question $.sys.country was right al along, there was just a problem in my Pentaho workflow (the REST step for getting the JSON was run more than once due to a number I didn't change). So now everything's working and I can get the values out of the JSON!

Links I found that solved my problem:

Hope this can help other people with the same problem too!

E. V. d. B.
  • 815
  • 2
  • 13
  • 30