jsonimport(
jsonimport(
JSON
)

The jsonimport( function converts JSON into a Panorama dictionary or data array.

Parameters

This function has one parameter:

json – The JSON text to be imported.

Description

This function converts JSON into a Panorama dictionary or data array. (JSON is a data interchange format that is used by many web APIs. See http://json.org for more information.)

For example, this formula creates a Panorama dictionary.

    jsonimport(||| {"Name" : "Jack", "Color" : "Blue", "Size" : 38} |||)

The example above is exactly equivalent to this formula that uses the initializedictionary( function.

    initializedictionary("Name","Jack","Color","Blue","Size",38)

This example creates a Panorama data array.

    jsonimport(|||["Orange", "Teal", "Yellow"]|||)

The example above is exactly equivalent to this formula that uses the dataarray( function.

    dataarray("Orange", "Teal", "Yellow")

Complex JSON text can include arrays nested within dictionaries, or vice versa. The jsonimport( function will create the equivalent nested structure using Panorama dictionaries and data arrays. You can use the getstructurevalue( function to extract specific items from this sort of nested structure.

##### Dealing with Null Values

The JSON format allows a data value to be unspecified, this is called a null value. This example includes a null color value.

    jsonimport(||| {"Name" : "Jack", "Color" : null, "Size" : 38} |||)

Panorama does not normally allow null data, so when the jsonimport( function encounters a null value in the JSON text, the function places an error value in the result dictionary (if using Panorama X 10.2 or later).

For example, suppose you have a database with fields for Name, Color and Size, and you wish to copy JSON data into these fields. This code will check to see if the color is null, and if so, prompt the user to enter a color.

    let data = jsonimport(||| {"Name" : "Jack", "Color" : null, "Size" : 38} |||)
    Name = getdictionaryvalue(data,"Name")
    Color = getdictionaryvalue(data,"Color")
    if error
        gettext Color,"Prompt","Please specify a color.",
            "Subprompt","(no color specified in JSON data)",
            "Button","Ok","AbortButton","Cancel"
        if info("dialogtrigger")="Cancel" rtn endif
    endif
    Size = getdictionaryvalue(data,"Size")

Another approach would be to use the catcherror( function to provide a default value in case the JSON includes a null value, like this:

    let data = jsonimport(||| {"Name" : "Jack", "Color" : null, "Size" : 38} |||)
    Name = catcherror("",getdictionaryvalue(data,"Name"))
    Color = catcherror("white",getdictionaryvalue(data,"Color"))
    Size = catcherror(36,getdictionaryvalue(data,"Size"))

Unlike the previous example, this code will work with a null value in any position of the JSON data.

See Also

- databaseexportjson -- exports selected records in the current database in JSON format to a text file.
- exportjson -- exports the current database in JSON format.
- getstructurevalue( -- extracts a value from a structure of nested Data Dictionaries and/or Data Arrays.
- importjson -- imports a JSON array into the current database.
- importjsonline -- imports a JSON record into the current record.
- jsonexport( -- converts a Panorama dictionary or data array into JSON.
- jsonexportline( -- exports the current database record in JSON format.
- jsonscriptstring( -- converts text into a JSON string literal
- newdatabasewithjson -- creates a new database using a JSON file.
- tsvtojson( -- converts tab separated text into JSON formatted text.

History

Version | Status | Notes
10.2 | Updated | This function now works with null values in the JSON data.
10.0 | New | New in this version.