Data Types
JSON does not use a unique data type. JSON values and objects can be stored in any string column: char, varchar, long varchar, byte, varbyte, and long byte. A database column containing JSON data looks and acts like a normal “non-JSON” column and can store a mix of JSON data and non-JSON data. When running JSON queries or expressions, however, JSON uses its own set of data types. A JSON value can have the following data types:
• JSON scalar
• JSON array
• JSON object
JSON Scalars
A JSON scalar can be a string, number, null, or Boolean.
Strings must be double quoted.
JavaScript escape rules for strings apply: a single quote (') in a double quoted string is represented as two consecutive single quotes. Example: "six o'' clock"
A backslash can be used to escape nested quotes. Any or all characters can be represented by using the string "\u" followed by four hexadecimal digits representing the UTF-16 surrogate pairs for representing Unicode characters. Examples:
"They said \"correct\""
"Fran\u00e7ois" for “François”
Floats can be entered in exponential format. Example: 1.23E08
A JSON null is different from an Actian X null value. A JSON null is considered a unique value of its own data type and is equal to another JSON null.
The comparison (NULL == NULL) returns TRUE. Actian X nulls used in JSON expressions are converted to JSON nulls.
A JSON Boolean is TRUE or FALSE.
JSON Arrays
A JSON array is a comma-separated list of JSON values surrounded by square brackets.
The values in an array can have different data types.
Array values are referenced by specifying an array name followed by a list of array indexes in square brackets.
An array index is used to reference an array value/element. Rules for array indexes are as follows:
• Array indexes can be single numbers. Floats are rounded down.
• The first element of an array is at index 0.
• "last" represents the last element of the array; "last-1", "last-2" are also permitted.
• The keyword “to” is used to represent a range. If
X to Y is used where X is greater than Y, it is an error in strict mode and returns no elements in lax mode. (For more information on modes, see
Lax and Strict Modes.)
• An asterisk (*) returns all elements of the array.
• Out of range indexes are ignored in lax mode and are errors in strict mode.
• In lax mode, scalars can be treated as one-element arrays.
Examples:
Let A1 be the array [0, 1.0, 2, "three", {"number":4}, [5,6]]
• A1[0] = 0
• A1[last] = [5,6]
• A1[1 to last-2] = 1.0, 2, “three”
• A1 [*] = 0, 1.0, 2, “three”, {“Number”:4}, [5,6]
The following are errors for strict mode and return the shown result for lax mode:
• A1[2.1, 10, 0 to 1, 2] = 2, 0, 1.0, 2 (in that order)
• A1 [*].Number = 4
• A1 [*][1] = 6
• A1 [*][0] = 0, 1.0, 2, “three”, {“Number”:4}, 5
• A1 [-1 to 1] = 0, 1.0
JSON Objects
A JSON object is a comma separated list of key:value pairs surrounded by braces {}.
A key must be a double-quoted string. A value can be any JSON value including a JSON object or JSON array. It cannot be blank.
Whitespace is ignored in a JSON object string except for whitespace within the double quotes of a string.
Example:
{ "type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [0, 90] },
"properties": { "name": "North Pole" }
}
}
Keys in a JSON object do not have to be unique. During key retrieval, however, only the first value is returned.
In addition, JSON objects are considered an unordered set of key:value pairs, so subsequent key retrievals may return the “other” duplicate key.
You can create a JSON object from table data using the JSON_OBJECT constructor, as described in
JSON_OBJECT.