I am attempting to pull in some USGS groundwater data from a large number of sites over a number of years. The USGS has a website that can publish a JSON service that I am attempting to consume in FME Workbench. Here is a LINK to the website. Every time I attempt to grab one site or 100 it fails in workbench using the JSON reader. The error is consistent at least. The feature reader in the log just says that the file doesn't exist. I'm not sure what I am doing wrong on the setup to bomb that quick in the process. Any guidance or info would be greatly appreciated.
Userlevel 6
+32
- Evangelist
- 2330 replies
-
30 June 2022
If you share the generated url it is easier to reproduce your issue.
+14
- Contributor
- 56 replies
-
30 June 2022
Sorry. I thought putting the website link to generate was a good start. Here is the URL:
//waterservices.usgs.gov/nwis/iv/?format=json&stateCd=va¶meterCd=00060,00065&siteStatus=all
and I've attached the file for the same JSON service.
+14
- Contributor
- 56 replies
-
30 June 2022
Also, I tried using the FeatureReader as an alternative, but it only pulls one record from that JSON file when it is years' worth of data.
+14
- Contributor
- 56 replies
-
5 July 2022
If you share the generated url it is easier to reproduce your issue.
Were you able to see the files / service I posted?
Userlevel 6
+32
- Evangelist
- 2330 replies
-
5 July 2022
Were you able to see the files / service I posted?
Sorry mate I missed your reply. Thanks for the url. I got no issues with one of the other generated urls that is why I asked for your specific url.
What are the objects you need as features? What I typically do is open de url in the browser, copy paste it to JSONLint to check if it is valid and format it. Then look at the structure and find what I need for features.
Then save the formatted text to a file and run it through the JSONFragmenter. You can easily find the object you need in the tree structure using the JSON Query "..." button. I think you need:
json["value"]["timeSeries"][*]Next steps are depending on what you need. Using JSONFragmenters / JSONExtractors you can build the datastructure how you want it.
Finally, if you want to read the data from online, simply replace the pathname in the initial JSONFragmenter with the url and you are done.
Userlevel 1
+10
- Participant
- 3078 replies
-
5 July 2022
In the feature reader you have to set schema scan mode to JSON Query and then set the parameters, e.g.
For the URL you provided this then reads 526 features
+14
- Contributor
- 56 replies
-
5 July 2022
In the feature reader you have to set schema scan mode to JSON Query and then set the parameters, e.g.
For the URL you provided this then reads 526 features
If there is data in two different places in the JSON service, how can you easily combine those results? I can extract them each by themselves, but when I grab both under the FeatureReader, they aren't combined for a single record. The three main fields I'm after are the site_no, data/time, and value. The values are under [timeseries][*][values][*][values][*], whereas the majority of the other data is under the timeseries[*] that you showed in your example. Any recommendations are appreciated. I tried to attach some screenshots to show what is going on. It looks like there are multiple recorded values per site. 
+14
- Contributor
- 56 replies
-
6 July 2022
In the feature reader you have to set schema scan mode to JSON Query and then set the parameters, e.g.
For the URL you provided this then reads 526 features
To clarify my question, can you string multiple JSON queries together? I have attempted to put them in separate rows in the JSON Query, and I get all the data, but it is broken out / disconnected in the table. I need the values to match the site, if that makes sense. I tried separating them with a comma or a semicolon on the same line, but that didn't work either. 
Userlevel 1
+10
- Participant
- 3078 replies
-
6 July 2022
If you have Read Entire Feature as JSON Fragment set to Yes in the FeatureReader, you will then get a json_fragment attibute which you can use the JSONFragmenter to extract the data you need using json["values"][*]["value"][*]
+14
- Contributor
- 56 replies
-
6 July 2022
If you have Read Entire Feature as JSON Fragment set to Yes in the FeatureReader, you will then get a json_fragment attibute which you can use the JSONFragmenter to extract the data you need using json["values"][*]["value"][*]
That is exactly what I was looking for. Thank you very much. I see you created the attribute "test" on the FeatureReader. Where is the attribute_fragment setting being passed? Is that the name you gave the Feature Type Key Name in the FeatureReader?
+14
- Contributor
- 56 replies
-
6 July 2022
I am asking specifically about that setting in the FeatureReader due to something getting rejected in the JSONFragmenter. I have included my summary annotation data as well.
Userlevel 1
+10
- Participant
- 3078 replies
-
6 July 2022
This is how i have the feature reader set up
The JSON Fragmenter then needs to reference the json_fragment attribute, not the url
+14
- Contributor
- 56 replies
-
7 July 2022
It was a cruddy setting I had selected down at the bottom of the FeatureReader that was throwing the whole thing off. Thank you so much, and I appreciate your patience.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.