JSON.Import

Imports data presented in the USoft JSON format.

Imports base64-encoded binary data (of the BLOB data type).

IgnoreGUK parameter has been added.

Processes the row elements found in the JSON document one-by-one in document order. The default behaviour is "UPSERT" handling:

• A record is INSERTed if no record with the same primary key is found in the target table.
• No action is taken if a record with the same primary key, and no different values in other columns, is found in the target table.
• A record is UPDATEd if a record with the same primary key, but different values in other columns, is found in the target table.
• No record is DELETEd.

This “UPSERT” handling may be modified by meta-instruction properties, namely the @usoft-json meta-instruction property supplied at the top of the import document, and @Insert, @Update and @Delete meta-instruction properties supplied inside record entries. It may also be modified by the VerifyOriginalValues and UseIOFormats parameters of JSON.Import itself.

Syntax

INVOKE JSON.Import WITH
SELECT      value    parameter
,           value    parameter ...
,           expression JSONDocument
FROM        table

The required SELECT output-list is made up of 0 or more value parameter pairs and 1 required (SQL) expression, which may be named by the optional JSONDocument alias. Possible values for the value parameter pairs are listed in the table in the Parameters section below.

Expression is usually either a filepath pointing to a file on the file system, in which case the optional FROM clause is typically omitted (Example 1), or an inline JSON document (Example 3 further down in this article). If expression refers to a database column (Example 2), the possible values for expression are:

column
OLD( column )

Other SQL functions than OLD() are not supported. OLD() is not typical in this context.

It is customary, but not mandatory, to mention value parameter pairs before expression.

Example 1

INVOKE  JSON.Import WITH
SELECT  'c:\temp\tours.json'

Example 2

INVOKE    JSON.Import WITH
SELECT    'yes'          UseIOFormats
,         description    JSONDocument
FROM      tour

Parameters

This table lists value parameter pairs supported in the SELECT output lists passed to JSON.Import. Possible values are listed by parameter; the default value (if any) is underlined. The use of each parameter is optional. If you do not specify a given parameter and it has a default value associated with it, that is the value applied.

Both value and parameter are case-insensitive.

VerifyOriginalValues

Setting the VerifyOriginalValues parameter allows optimistic locking mechanisms and prevents lost updates if different client applications change the same information concurrently.

The VerifyOriginalValues parameter has an effect only on records in the JSON data that contain an Update or Delete instruction tag. It determines whether or not the Rules Engine will perform 2 types of check before attempting the import instructions:

• A check whether a record with the primary key value mentioned in the JSON actually exists in the database.
• A check that old values mentioned in the JSON are the same as the old values in the database record.

If any of these checks is performed and fails, a blocking error is raised, causing the import operation to be unsuccessful.

Old values are non-primary-key values mentioned in the JSON or found in the database before the Update or Delete. In the USoft JSON format, old values are optional. By default, in an Update instruction, the old values are mentioned for each of the columns that are to be updated.

The values of the VerifyOriginalValues parameter have the following meaning:

Example 3

In this example, a blocking error is raised if one or more of the following apply:

• There is no PERSON database record with ID = 112.
• There is no PERSON database record with ID = 203.
• There is a PERSON database record with ID = 112, but either its FAMILY_NAME value is other than "Smith" or its FIRST_NAME value is other than "John" (or both).
• There is a PERSON database record with ID = 203, but either its FAMILY_NAME value is other than "Sutcliff" or its FIRST_NAME value is other than "Bill" (or both).

INVOKE    JSON.Import WITH
SELECT    '{ "PERSON":T
                "ID":112
             ,  "FAMILY_NAME":"Smith"
             ,  "FIRST_NAME":"John"
             ,  "@Update":{
                   "FIRST_NAME":"Johnny"
                }
             ],
             "PERSON":]
                "ID":203
             ,  "FAMILY_NAME":"Sutcliff"
             ,  "FIRST_NAME":"Bill"
             ,  "@Delete":{}
             ]
          }' JSONDocument
,         'AllColumns' VerifyOriginalValues

The returning JSON object

The JSON.Import method returns a result JSON object.

This object contains an entry for each record that has been inserted or updated directly by the import. Column values shown in the result JSON are those that apply after the record has been processed:

• Sequence numbers have been calculated.
• Productive single-record and multi-record constraints have executed.

Since only records inserted or updated directly by the import are included, any effects that productive multi-record constraints may have had on other records are not visible in the result document.

If the import has not caused any record to be inserted or updated, the result document contains only a root element.