Jsonimport( seems to be choking on "null"

I have been trying to learn how to use APIs, but my test returns data with value “null” which is a reserved word in json. At best, jsonimport( chokes on it. Typing jsonimport(|{“test”:null}|) in the Formula Workshop causes Panorama X to crash.

Interesting, I was not aware of that. Panorama has no way to represent a null value. To prevent the crash, I have modified the converter to convert null into zero bytes of binary data. This fix will be in version 10.2.

I am not certain what “null” should represent when it is converted into a dictionary. I suppose it depends on context.

Some languages have a separate representation for “null”, where “null” indicates the absence of any value. Panorama does not have any representation to indicate the absence of any value, and I don’t think it makes sense to add such a representation just for the purpose of dealing with JSON data.

I thought a workaround would be to replace the word “null” with “”, but I cannot be certain that the word “null” does not appear in a string, so it is a little more complicated than that.

It struck me that jsonexport( should be checked to see that absent values are exported to “null”. I have not tested that.

As I mentioned, Panorama does not have the concept of an absent value, so actually your proposed test would be impossible to perform. The output from jsonexport( can never contain null, because in Panorama, if a value exists, it cannot have a null value. So there is no way to pass a null value to jsonexport(.

After ruminating on this for a while, I think it might be possible to allow null data to be stored in variables, dictionaries and/or data arrays (not in a database however). I have made a note of this in Bitbucket, however, this is a pretty low priority item.

For the moment, I have replaced “null” with “N/A” for my purposes, ignoring the possibility that the word “null” appears within a string. Null values certainly can appear in Panorama, usually as empty strings.

For what it might be worth, the ASCII null character is chr(0). I’m guessing that since it is used as the end of string character in C and C+ that it might cause problems somewhere along the lines. Kind of out of my pay grade on this one so I’ll leave it at that.

I’m sorry, but this is incorrect. An empty string is NOT null. Null means no value at all. An empty string has a value - text of zero length.

In Panorama, the closest you can come to this concept is a variable with no defined value. Consider this code:

local x

After this line of code, the variable x exists, but it has no value, in other words, it is null. It’s not empty text, it’s not zero, there is no value at all.

Panorama treats null as an error, so if you try to use this value, you’ll get an error.

A = x

With another line of code, we can assign a value to x.

x = ""

Now, the value of x is not null, it is empty text. If we try the assignment again, it will work fine.

A = x

Bottom line – JavaScript treats null as a different thing than empty text or an empty array. Panorama does not currently have any way to do that. My Bitbucket proposal outlines a possible method to add this capability to Panorama.

I’ve allowed this issue to distract me today. The issue is now completely resolved in Panorama 10.2. To see how it will work, you can view these help pages on the web, look for the Dealing with Null Values section on each of these pages.

There is additional info about how this was done on the Bitbucket page for this issue.

1 Like

In the jsonimport function help, the last line “Unlike the preious example, this code will work with a null value in any position of the JSON data.” should be “previous.”

In jsonexport and jsonimport, “(JSON is a data interchange format that is used by many web APIs. See [http://www.json.org] for more information.)” has an extra pair of brackets.

Thanks, got it.

There are a couple more extraneous brackets in jsonexportline(. I guess that is what is keeping the URLs from appearing as links.

I don’t see any further problem, the URL to the json.org website appears as a link to me. If you see something I’m not seeing, please give an exact location.

jsonexportline(
jsonexportline()
The jsonexportline( function exports the current database record in JSON format.

Parameters

No parameters.

Description

This function exports the current database record in JSON format. (JSON is a data interchange format that is used by many web API’s. See [http://www.json.org][] for more information.)

Panorama has two data types that are not directly supported by JSON – dates and binary. Dates are exported as text, using the pattern YYYY-MM-DDT00-00-00Z. Here are some examples:

June 9, 2016 ☞ 2016-06-09T00-00-00Z
December 15, 2017 ☞ 2017-12-15T00-00-00Z
March 1, 1987 ☞ 1987-03-01T00-00-00Z
Binary data is exported as text, encoded in Base64 format. For more information on Base64 encoding see [Base64 - Wikipedia][].

Neither of the URLs in bold show up as links in the Help of either of my computers.

Those are both coded in Markdown as if they were links to pages within Panorama’s application bundle, rather than pages on an external server.

[http://www.json.org][]

should be

[http://www.json.org](http://www.json.org)

and

[https://en.wikipedia.org/wiki/base64][]

should be

[https://en.wikipedia.org/wiki/base64](https://en.wikipedia.org/wiki/base64)

I’ve submitted corrections for that and a few others I found after searching for pages that contained [http in their text. Most of the pages I found with that search were either already correct, or had already been corrected.

Oops, I didn’t notice that you mentioned a different page, one that hadn’t been mentioned previously on this thread.

Thank you! Searching for [http was an inspired idea.

I just do not know Markdown well enough to know what was happening, and trying to learn that and how to use APIs at the same time is too much in too little time! But it gets done, with the help of the community.