Failsafe JSON parsing

[#1]

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]"}

[#2 · Accepted solution]

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.

[#3]

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!

[#4]

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

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