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

The importjson statement imports a JSON array into the current database.

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 existingdata, 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 array into the current database. The JSON array must contain one or more JSON records, each of which will be imported into an individual Panorama record. (JSON is a data interchange format that is used by many web APIs. See http://www.json.org for more information.) The imported records are normally appended to the end of the database, but you can also replace the current database contents with the JSON data.

Here is some typical JSON data. In this example there are four records. Notice that different records contain different items – that is perfectly fine (you’ll also notice that the order of the items within each record is unimportant).

[
  { "Name" : "Bob Smith", "Email" : "bsmith@mail.com", "City" : "Tustin" },
  { "Name" : "Margo Wilson", "Email" : "mw@zmail.com", "City" : "Placentia" },
  { "Name" : "Ted Jarvis", "Phone" : "(714) 598-2847", "Wage" : 12.50 },
  { "Phone" : "(714) 348-9033", "Name" : "Bill Jackman", "Email" : "wjack@zmail.com", "City" : "Brea" }
]

Suppose the text above was in a file in your Documents folder named Contacts.json. You can create a new Panorama database containing this data with this two line procedure:

newdatabase
importjson fileload("~/Documents/Contacts.json")

The first line, newdatabase, creates an untitled database with one field (A) and one record. The second line, importjson, imports the four records into the new database. In the process, it creates 4 text fields (Name, Email, City and Phone) and one floating point field (Wage). Note: Since the original database was empty, the original field (A) is removed.

Now suppose you have a second file named Contacts2.json.

[
  { "Name" : "Jordan Kelso", "Email" : "jkelso@mail.com", "Wage" : 11.75, "Phone" : "(562) 479-3920 },
  { "Name" : "Milo Sparks", "Phone" : "(714) 490-3920", "Wage" : 14.50, "Organization" : "Sparks Cleaning" },
]

You can add this data to the new database with another importjson statement.

importjson fileload("~/Documents/Contacts2.json")

This statement will add two more records to the database, and one more field (Organization).

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.

importjson ||| [
  { ... },
  { "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 importjson 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).

importjson ||| [
  { ... },
  { "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 importjson statement, like this:

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

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

EXISTINGDATA

The importjson statement normally appends the imported data to the end of the current database. You can use this option to tell it to replace the data instead.

importjson Presidents,"ExistingData","Replace"

Be careful! This will erase all of the existing data in the database! (Note: The replace option is not available for shared multi-user databases, attempting to use this option will cause an error.)

If you want to append the imported data, use append instead of replace (or just leave this option off).

ADDFIELDS

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

importjson jsontext,"addfields","no"

Note: If you are importing into a shared multi-user database (connected to a server), you must disable this option, since fields can’t be added to a shared database (except through the “new generation” process.)

MISSINGFIELDSERROR

If the JSON text contains items that don’t have corresponding database fields, the importjson 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 option, like this:

importjson jsontext,"addfields","no","missingfieldserror","no"

MAPFIELDS

The importjson 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" : 25 },
    { "Product" : "Right Handed Bob Wrench", "Quantity" : 85 },
    { "Product" : "Thor Hammer", "Quantity" : 15 }
] |||
importjson 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" : 25, "Vendor" : "Giant Conglomerate" },
    { "Product" : "Right Handed Bob Wrench", "Quantity" : 85, "Vendor" : "Giant Conglomerate" },
    { "Product" : "Thor Hammer", "Quantity" : 15, "Vendor" : "Norwegian Tools" }
] |||
importjson 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 importjson 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.2UpdateIf the database is shared (multi-user), you cannot use the ADDFIELDS or REPLACE option with this statement.
10.0NewNew in this version.