cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
burakbagdatli
Level III

Failsafe JSON parsing

I have a string column that has JSON payloads in it. This is the way our partner stores messages from automated machines. The trouble is that their database sometimes gets non-JSON messages so my :payload column has rows that cannot be parsed to JSON simply.

 

I wrapped my parse function with a Try block but even then I'm getting an alert for each row that includes non-JSON-compliant string. It even crashes JMP14 fully sometimes. I could ignore errors but that's not a great way of writing robust code.

Local(
  { parsed = [=> ""] },
  Try( parsed = Parse JSON( :payload ) );
  parsed;
);

This fails so badly. In rows where :payload is empty instead of having a JSON string, it returns a "." for some reason. I have no idea where the dot comes from.

 

Is there a way to use JSON parsing functions in a failsafe manner? Any help is appreciated.

 

 

 

Note:

Here's some examples of the kinds of JSON that I'm dealing with and they get parsed properly:

{"assetId":"AthensPaintLineMotor4",
"dataItemId":"Temperature1",
"dateTime":"2018-02-22T19:44:41",
"value":"91.51"}

 

{"assetId":"OKUMA-Genos-2","dateTime":"2018-04-30T11:59:13","dataItemId":"Lp1block","dataItemName":"p1block","value":"UNAVAILABLE"}

 

{"assetId":"OKUMA-Genos-2","dateTime":"2018-04-30T11:59:13","dataItemId":"Lp1MacManPanelHistory","dataItemName":"p1MacManPanelHistory","value":"2018/04/30 07:58:12 @Change Window:[26026]"}

What we see of the real world is not the unvarnished real world but a model of the real world, constructed so it is useful for dealing with the real world. —Richard Dawkins
1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales
Super User

Re: Failsafe JSON parsing

Some of this is fixed in the upcoming 14.1 maintenance. If you have data that crashes JMP and can share it, that would help. Here's a work around for now:

payload = "\[
	{
]\"; // malformed JSON
Try(
	txt = Log Capture( result = Parse JSON( payload ) );
	If( Length( txt ),
		Throw( txt ) // error message
	);
	result; // parsed JSON
,
	Show( exception_msg );
	"?" // returned value for malformed JSON
);

This works by using the LogCapture function to capture any log output from the Parse JSON function and throwing that as an error. The Show(...) is optional; exception_msg is a special variable with the value that was thrown, either by throw(txt) or by JMP for some other error.

The "?" could be replaced by [ => ] or some other value that makes sense to you.

In the payload string I used the raw string quotes "\[ and ]\" so I would not need to escape quotation marks in my test data. You don't need that for data already in a data table.

Craige

View solution in original post

3 REPLIES 3
Craige_Hales
Super User

Re: Failsafe JSON parsing

Some of this is fixed in the upcoming 14.1 maintenance. If you have data that crashes JMP and can share it, that would help. Here's a work around for now:

payload = "\[
	{
]\"; // malformed JSON
Try(
	txt = Log Capture( result = Parse JSON( payload ) );
	If( Length( txt ),
		Throw( txt ) // error message
	);
	result; // parsed JSON
,
	Show( exception_msg );
	"?" // returned value for malformed JSON
);

This works by using the LogCapture function to capture any log output from the Parse JSON function and throwing that as an error. The Show(...) is optional; exception_msg is a special variable with the value that was thrown, either by throw(txt) or by JMP for some other error.

The "?" could be replaced by [ => ] or some other value that makes sense to you.

In the payload string I used the raw string quotes "\[ and ]\" so I would not need to escape quotation marks in my test data. You don't need that for data already in a data table.

Craige
burakbagdatli
Level III

Re: Failsafe JSON parsing

I'm glad to hear that this will be changed in 14.1. With these changes I still get the endless "alert popups". Because the data table is millions or rows long, clicking on the OK sometimes crashes JMP14. There's nothing special about the data. In fact I subsetted a small part as a different table and while the same alerts are happening on that, JMP survives the problem. :)

 

Anyway, I'm going to find a simple workaround for this. The non-standard JSON comes in a few predictable forms. If I can if-else these conditions it'll work fine.

 

Thank for the help!

 

What we see of the real world is not the unvarnished real world but a model of the real world, constructed so it is useful for dealing with the real world. —Richard Dawkins
Craige_Hales
Super User

Re: Failsafe JSON parsing

Try the Batch Interactive solution @vince_faller discussed here. (And @Eric_Hill reply.)

Scriptscriptscriptscript;
Batch Interactive(1);
DatabaseCall;
Batch Interactive(0);
MoreScriptScriptScript
Craige