Panorama > Code > Program Access to Databases
 
importjsonline
importjsonline
JSON
,
OPTION
,
VALUE

The importjsonline statement imports a JSON record into the current record.

Parameters

This statement has three parameters:

json – JSON record to import.

option – An option for customizing how JSON data is imported. The available options include addfields, missingfieldserror and mapfields. See below for descriptions of each these options. The option name can be specified in either upper or lower case.

value – The new value for this option.

Description

This statement imports a JSON record into the current record, replacing whatever is already in the record. The JSON parameter must contain a JSON record (surrounded with { and } characters). (JSON is a data interchange format that is used by many web APIs. See http://www.json.org for more information.)

Here is a basic example that shows how this works. In this case the JSON data contains 7 items – 5 text items, two numeric (one floating point, one integer).

 importjsonline ||| {
     "Last" : "Wilson",
     "First" : "Smith",
     "Address" : "289 Markle Place",
     "City" : "Pleasantville",
     "State" : "AX",
     "Wage" : 12.50,
     "Vehicles" : 2
     } |||

The importjsonline statement will assign each value to the corresponding field in the database. If the field doesn’t exist, it will be automatically created with the appropriate type (this can be disabled, see below).

Importing Dates

When data is imported into a pre-existing date field, Panorama will process the data just as it would data entry into the field, so it will accept data like 6/19/17, Jan 5, 2001, or even today or next tuesday. So if the database already contains a field named Hired, this example will import February 4, 2007 into that field.

 importjsonline ||| {
     "Last" : "Wilson",
     ...
     "Hired" : "2/4/07"
     } |||

What if the Hired field doesn’t already exist? In that case, Panorama has no absolute way to make sure that information is a date (perhaps 2/4/07 is a part number). Because it can’t be certain, the importjsonline statement will create Hired as a text field. You can manually change this into a date field later.

There is one case where Panorama will create a date field automatically. Many JSON applications (including Panorama itself) encode dates using the format:

 YYYY-MM-DDTHH-MM-SSZ

If the importjsonline statement encounters a value in this format, it will automatically create a date field instead of a text field. (However, it will only do this if the time is midnight, as shown in this example).

 importjsonline ||| {
     "Last" : "Wilson",
     ...
     "Hired" : "2007-04-27T00-00-00Z"
     } |||

The example above will automatically create a date field named Hired if it doesn’t already exist.

IMPORT OPTIONS

In addition to the text to import, you can also specify multiple option/value pairs in a single importjsonline statement, like this:

importjsonline text,option1,value1,option2,value2,option3,value3

The possible options are addfields, missingfieldserror and mapfields. Each of these options is described in detail below.

ADDFIELDS

The importjsonline statement normally adds new fields as necessary to accomodate all the data in the JSON record. However, you can use this option to disable that.

importjsonline jsontext,"addfields","no"

If the JSON text contains items that don’t have corresponding database fields, the importjsonline statement will stop the procedure with an error. If you want to ignore the error you can use if error, or you can use the "MISSINGFIELDSERROR" parameter, like this:

importjsonline jsontext,"addfields","no","missingfieldserror","no"

MAPFIELDS

The importjsonline statement normally assumes that items in the JSON record have the same name as database fields. For example, it will place the JSON Product item into the database Product field, and the Quantity item into the Quantity field. But what if the names don’t match? Perhaps your database fields are named SKU and Qty instead of Product and Quantity. The MAPFIELDS option allows you to specify which JSON fields match which database fields. The mapfields value must contain a line for each field. The line starts with the JSON item name, followed by the → character, followed by the database field name. (You can copy the → character right from this page.)

JSON Item 1→Database Field 1
JSON Item 2→Database Field 2
JSON Item 3→Database Field 3

In this example, the Panorama database contains fields named SKU and Qty. The JSON items Item and Quantity will be copied into these fields. (Note: If the fields don’t exist, they will be created automatically.)

local jsontext
jsontext = ||| { "Product" : "Left Handed Bob Wrench", "Quantity" : 75 } |||
importjsonline jsontext,"mapfields",commatocr("Product→SKU,Quantity→Qty")

The MAPFIELDS option can also be used to ignore certain JSON items. To do that, simply map the database field name to empty. In this example, the JSON vendor item will be ignored.

local jsontext
jsontext = ||| {
    "Product" : "Left Handed Bob Wrench", 
    "Quantity" : 75,
    "Vendor" : "Giant Conglomerate"
    } |||
importjsonline jsontext,"mapfields",commatocr("Product →SKU,Quantity→Qty,Vendor→")

Complex JSON Items

In all the examples shown so far the JSON values have been simple fields or numbers. These simple values are straightforward to import into Panorama fields. But JSON values can also be complex values built up of multiple items. If this type of JSON value is encountered, the importjsonline statement will encode the data in a binary field using either a Panorama dictionary or data array.

Consider this JSON record, which contains a subarray for children:

{
    "Name" : "Melanie Billings",
    "Spouse" : "Robert",
    "Children" : [ "Mark", "Naomi", "Victoria" ]
}

If imported into a Panorama database, the data will flow into three fields: Name, Spouse and Children. The Name and Spouse fields are ordinary text fields, but Children will be a binary field that contains a data array. Different children can be retrieved with the array( function.

array(Children,1) ☞ Mark
array(Children,2) ☞ Naomi
array(Children,3) ☞ Victoria

After importing, you could create a new text field called ChildNames, and then transfer the names into this field in a comma separated format using the exportdataarray( function:

field ChildNames
formulafill exportdataarray(Children,",")

The previous example included an array, now let’s look at a JSON record that contains a subrecord for phone numbers.

{
    "Name" : "Tonya Adams",
    "Title" : "Editor",
    "Phone" : 
        {
            "Work" : "(847) 442-9283",
            "Home" : "(847) 309-2284",
            "Cell" : "(903) 662-3022"
        }
 }

The phone number information will flow into a Panorama binary field named Phone. Different phone numbers can be extracted with the getdictionaryvalue( function.

 getdictionaryvalue(Phone,"Cell") ☞ (903) 662-3022
 getdictionaryvalue(Phone,"Home") ☞ (847) 309-2284
 getdictionaryvalue(Phone,"Work") ☞ (847) 442-9283

Use the listdictionarykeys( function to get a list of all of the available phone numbers.

See Also

History

VersionStatusNotes
10.0NewNew in this version.