Reading JSON data with a REST API

[#1]

Problem

Some web sites provide a REST API to read data, and often the response is text in JSON format. How do we get that into JMP?

Solution

Here is a example using the meetup.com REST API. It's relatively simple since it uses the common "GET" method, which is the default for web page reading. More elaborate messages use the "POST" method which would require a different approach. With the GET request, the inputs are appended to the URL itself.

my key = load text file("meetupkey.txt");  // I stored my key in a text file
event id = "246678392"; // the meetup id at the end of the event's URL

url = "https://api.meetup.com/2/rsvps?key=" || my key || "&event_id=" || event id;
s = load text file( url ); // s contains the JSON result as a big string

// We could store that in a file and then open the file, but here's a shortcut.
// The Open() function can read data directly from a blob -- you just have to tell it the file type
b = char to blob(s);
dt = open(b, "JSON");
// JMP interprets the JSON structured tree into a flat data table, with surprisingly good results.

Discussion

After reading the data, I often do some clean-up and create convenience columns, such as:

dt << New Column("reply time", formula(:results.mtime/1000 + 01Jan1970), format("ddmonyyyy"));
dt << New Column("count", formula(if(:results.response=="yes", :results.guests + 1, 0)));

Having this in a scripts makes it easy to refresh my data as needed. Now I'm ready to do some exploration..

[Craige_Hales]

Cool! 

This is easier than the code I used here. In JMP 13 it can be as simple as

Open( "http://api.open-notify.org/iss-now.json?r=" || Char( Random Integer( 1, 1e6 ) ), "JSON" );

International Space Station position

Note the random number trick: the URL looks different every time so you don't get an old, stale, cached copy of the data. The ?r= at the end starts a parameter named r. This web site doesn't need any. If there were named parameters, like "?time=3:00&place=space" you can add your own parameter name that the site will ignore: "?time=3:00&place=space&r=" (tack on the random number here).

In JMP 12 you'll get the JSON string: "{\!"iss_position\!": {\!"latitude\!": \!"-41.6934\!", \!"longitude\!": \!"-140.2738\!"}, \!"timestamp\!": 1518704338, \!"message\!": \!"success\!"}"

[hyue]

I am trying to submit POST requests with tabular data formatted as JSON.  The following code echos the POST request with the inputs as args, similar to a GET request.

url = "http://httpbin.org/post";
request = New HTTP Request(
	URL( url ),
	Method( "Post" ),
	Query String(
		[[
			"key1" => "value1", "key2" => "value2"
		]]
	)
);
 
json = request << Send();
 
jsl_json = Parse JSON( json );
Write( jsl_json );

However, I want to POST with data in JSON format.  Here is how I would do it from the Mac OSX Terminal:

curl -d '{"table1":[{"id": 1,"value1": 10,"value2": 11},{"id": 2,"value1": 20,"value2": 21}],"table2":[{"id": 3,"value1": 30,"value2": 31},{"id": 4,"value1": 40,"value2": 41}]}' -H "Content-Type: application/json" -X POST http://httpbin.org/post

I suspect I need to replace "Query String", but the documentation is not clear about what to replace it with.  Any guidance would be appreciated.

Thanks in advace!

Howard

[bryan_boone]

Hey Howard,

Typically when you use the "POST" HTTP Method you are "post"-ing form data.

The exact Method is defined by the web service's RestAPI since they can interpret "POST", "PUT", "GET", etc to imply certain things. (like this: https://www.w3schools.com/tags/ref_httpmethods.asp)

When you use HTTP Request (as in your example), Query String becomes part of the url (like:

"http://httpbin.org/post?key1=value1&key2=value2")

In your example, if you want to POST data, you'd do this:

url = "http://httpbin.org/post";
request = New HTTP Request(
	URL( url ),
	Method( "Post" ),
	Form(
		Fields([[
			"key1" => "value1", "key2" => "value2"
		]])
	)
);
 
json = request << Send();
 
jsl_json = Parse JSON( json );
Write( jsl_json );

Query String gets replaced with Form.

Form has two items in it, Fields, and Files, both of which are Associative Arrays.

Alternatively, you could compose your code like:

url = "http://httpbin.org/post";
my_fields = [=>];
for(i = 1, i <= 2, i++,
	my_fields["key" || Char(i)] = "value" || Char(i);
);

request = New HTTP Request(
	URL( url ),
	Method( "Post" ),
	Form(
		Fields(my_fields)
	)
);
 
json = request << Send();
 
jsl_json = Parse JSON( json );
Write( jsl_json );

depending on how you want to create your key/value pairs for the Form.

-Bryan

[hyue]

Hi Bryan,

Thanks very much.  I'm able to successfully send and receive the Form, and will attempt to work this into my script.

Cheers,

Howard

[bryan_boone]

Fantastic.

I'll be posting more [concrete] examples in the JMP Scripts section.

-Bryan

[noemireyes]

Any chance this could be turned into a tutorial video?