How to parse a JSON file opened from URL?

[#1]

I would like to import a JSON file into JMP that is downloaded off of the web. The best way I've been able to do this is through the GUI, manually selecting File --> Open --> and entering the URL into "File Name". If I select "JSON Data File (*.json)" from the file type list, JMP will download the file and open the parsed data into a new datatable. Unfortunately, I haven't been able to write a script that replicates this process.

A simple open("URL") command saves the JSON content of the URL to the log and does not open a new datatable. I tried amending the code to: open("URL","json"), but this returns an error and no data are received.

I can run save text file("output path/filename.json",open("URL")) and then open("output path/filename.json") and JMP will automatically parse the file into a new datatable. This workaround accomplishes my goal, but the processing time is substantially longer than the manual file-->open process described in the previous paragraph. Many of the URLs I'm trying to import contain large amounts of data, so I'd like to minimize processing time as much as possible.

How can I download a JSON file directly from a URL and have the data open in a correctly parsed table?

I am running JMP 13.0.0 on windows.

[#2 · Accepted solution]

You can add the "JSON" option to the Open function to force the JSON file to be opened as a data table.

Open( "http://date.jsontest.com/", JSON );

Here's a comparison from the log when using this option vs not using the option:

open("http://date.jsontest.com/")
/*:

"{
   \!"time\!": \!"01:21:07 PM\!",
   \!"milliseconds_since_epoch\!": 1505827267889,
   \!"date\!": \!"09-19-2017\!"
}
"
//:*/
open("http://date.jsontest.com/", JSON)
/*:

Data Table( "date.jsontest.com" )

[#3]

Thanks for your reply, Justin. I ran your example code and received the following error:

//:*/
open("http://date.jsontest.com/", JSON)
/*:

Unexpected "M". Perhaps there is a missing [ or {.
Line 1 Column 1: ►M",

Tried to open: http://date.jsontest.com/

Cannot open table in access or evaluation of 'Open' , Open/*###*/("http://date.jsontest.com/", JSON)

In the following script, error marked by /*###*/
Open/*###*/("http://date.jsontest.com/", JSON)

I attempted to add the ",JSON" extension to my URL and received the following error:

//:*/
open("my URL",JSON)
/*:

Unexpected "sponseTime". Perhaps there is a missing [ or {.
Line 1 Column 1: ►sponseTime":"2017-09-19T13:33:38.9224228+00:00","S

Tried to open: my URL

Cannot open table in access or evaluation of 'Open' , Open/*###*/(
"my URL",
JSON)

In the following script, error marked by /*###*/
Open/*###*/(
"my URL",
JSON)

Thoughts?

[#4]

I just tried in JMP 13.0.0 and replicated the error you are seeing. I am using JMP 13.2 and there is no issue.

Would you be able to update to 13.2 and try it again? You can download the updater at jmp.com/update.

[#5]

I'll work on updating my software.

I'm still not sure why, using JSL, I can't replicate the GUI file-->open process that correctly handles my URL and opens a new datatable. There must be some way to script this procedure, right?

[#6]

The json support may have been added after 13.0. I think the file you are getting is probably equivalent to using "text" rather than "json".

open("http://date.jsontest.com/", "text")

produces this table:

JSON data, not split up very well

in JMP 13.2, using 

open("http://date.jsontest.com/","json")

I get a much more useful table:

JSON data, split into columns

I think if you specify "text" you'll probably get the table you see from File->Open. I hope you can upgrade to 13.2 and take advantage of the better JSON import.

[#7 · Accepted solution]

I updated my software to v13.2 and can now open my URL and have it correctly parsed into a new datatable using

open("my URL","json")

Executing this command is still substantially slower  than the GUI file-->open command (the processing time is about twice as long). When using the GUI, a dialog box pops up showing the download status of the file, information I do not receive with the open() JSL command. Even before my upgrade, the GUI file-->open was able to quickly open the URL and correctly parse the file (the output was different than open("my URL",text)). Is there any way to exactly replicate the file-->open process using JSL? Is there a way to record the code generated when executing file-->open (similar to the way recording a macro in excel captures the code generated by using the GUI)?

[#8]

Glad you got the update.

Can you post a picture of the tables? If there is a source script, post that too. The source script will have a temporary file rather than the URL; the open is always done from a file that was downloaded from the URL. You should be able to replace the temporary file name with the URL. It is possible that JMP 13 uses a different method to download the URL to a temp file when the menu vs JSL is used and might be faster. It is also possible caching might be involved. 

When the .json file goes through the CSV import code, the commas and newlines in the .json file are going to determine the shape of the data table. If the .json file writer decides to put in different newlines, you'll get a different data table. Using the .json import will help make your code more robust against changes in the .json file.

The open() function might be able to use a file extension to determine the file type. The extra parameter to open will override the file extension and should probably be used with a URL, especially if the URL might have

?parameter=value

appended to the end.

[#9]

JMP 13.2 gives you a bit more control over how the file is opened. Uunfortunately, it's not too obvious that you can control the behavior.  You should be able to get what you need. 

If the incoming file extension is unknown to JMP, we'll next check the  file type drop-down to the right of the edit field. If that has a selection which is not ambiguous then we'll open the file assuming it's a file of the type selected. If the file type dropdown selection is ambiguous (ex. All JMP Files or All Files), we ask the OS to open the file.

So, in your case you can try this:

* Paste the URL

* Change the dropdown from "All JMP Files" to either "Text Data" or "JSON Data File"

* [Optional] You may want to un-check the "Select this filter the next time..." if you don't want it remembered.

* Click OK

The OS dialog will download the file to a temporary location with a crazy name like FSERXVJW with no extension. JMP will try to open the file with the kind you selected in the drop down.

I am interested to know if having the dialog download your json and opened with the "JSON Data File" choice is faster than the Open("URL","json"); route. We may have perf work to do in that case...

John

[#10]

Clearly, I didn't read your original posting to thoroughly!

You did indeed know that changing to JSON data was the way to go.... I'm glad you have the JSL equivalent from the other replies and it does sound like we have some performance work to do to catch up with the download time of the Open dialog.

John