JSON in TimesTen

About JSON in TimesTen

TimesTen supports JSON through the Oracle JSON library from Oracle AI Database 26ai. This support enables you to:

Store JSON data in a table in TimesTen database.
Use JSON tables as a row source.
Extract values from JSON data and map them to SQL types.
Generate JSON data from existing relational data.
Send and receive JSON data to and from a TimesTen database.

TimesTen is an in-memory relational database that provides high-performance, low-latency data access. When combined with JSON data, TimesTen provides several benefits, including:

Improved performance: You can take advantage of the in-memory storage and caching capabilities of TimesTen to optimize the storage of JSON data, which results in faster query execution.

The in-memory storage of TimesTen ensures that the JSON data is readily available and minimizes latency.
Enhanced flexibility: JSON data allows for dynamic schema changes, and enables you to store and manage variable data structures, such as nested objects or arrays.
Simplified data management: You can store both structured and semi-structured data in a single database.

JSON Data Type

TimesTen supports JSON data through the SQL data type JSON. TimesTen uses Oracle Binary JSON (OSON) to both store and send JSON data. OSON is an optimized native binary storage format that Oracle Database uses for JSON data. TimesTen uses OSON rather than textual JSON for the following reasons:

Faster parsing and generation: OSON is parsed and generated faster than textual JSON because it eliminates the need for string manipulation and parsing.
Richer type system: OSON supports data types like DATE and TIMESTAMP, unlike textual JSON where these kind of data types are converted to string JSON values.
Smaller storage requirements: OSON typically requires less storage space than textual JSON due to its compact binary representation.
Optimized network transfer: The smaller storage requirements from OSON also reduce the network transfer requirements for JSON data, resulting in faster data transmission and lower bandwidth usage.
Improved query performance: OSON enables faster query execution by allowing TimesTen to directly access and manipulate JSON data without requiring additional parsing steps.
Better data integrity: OSON ensures data integrity by enforcing strict typing and validation rules during creation and modification, reducing errors caused by invalid or malformed JSON data.
Efficient indexing: OSON allows for efficient indexing, enabling fast look-up and retrieval of JSON data.

You can convert textual JSON data to JSON type data by parsing it with the JSON data type constructor. Conversely, you can convert JSON type data to textual JSON by using the JSON_SERIALIZE SQL/JSON function.

You can create a JSON type instance with a scalar JSON value by using the JSON_SCALAR SQL/JSON function. The value can be an Oracle JSON language type, such as DATE—which is not part of the JSON standard. Conversely, you can convert JSON type data into a SQL type instance by using the JSON_VALUE SQL/JSON function.

See also:

JSON Data Type in Oracle TimesTen In-Memory Database SQL Reference

JSON Data Type Constructor

The JSON data type constructor takes a textual JSON value as input (a scalar, object, or array), parses it, and returns the value as an instance of JSON type. Textual JSON data you use to perform INSERT or UPDATE operations on a JSON type column in TimesTen is parsed implicitly by the constructor—you need not use the constructor explicitly. The constructor also automatically makes an IS JSON check on the textual JSON data.

The input to the construct can be either a literal SQL string or data of type VARCHAR2, CLOB, or BLOB. A SQL NULL value as input to the construct returns a JSON type instance of SQL NULL.

The construct can return some, but not all, of the JSON values supported by Oracle JSON. This includes values of the standard JSON-language types: object, array, string, Boolean, null, and number. It also includes the following non-standard Oracle scalar JSON values: double (BINARY_DOUBLE), float (BINARY_FLOAT), binary (RAW or BLOB), date (DATE), and timestamp (TIMESTAMP).

If the input to the construct is malformed JSON data or a JSON object has duplicate top-level name fields, the construct returns an error. The construct supports lax JSON syntax, but the input data must conform to RFC 8259.

See also:

JSON Data Type Constructor in Oracle TimesTen In-Memory Database SQL Reference

Strict and Lax JSON Syntax

Textual JSON can be well-formed using either strict or lax syntax. This distinction applies while textual JSON is parsed. It does not apply to JSON values stored in a TimesTen column of type JSON. TimesTen stores those values in Oracle Binary JSON (OSON)—a binary representation rather than a textual representation.

When TimesTen parses textual input, strict syntax requires standard JSON forms. Lax syntax can accept some JavaScript-style forms, such as unquoted field names, single-quoted strings, case variations of true, false, and null, trailing commas, and permissive numeric forms. The TimesTen JSON data type constructor uses lax syntax for textual input by default.

The JSON_EXISTS condition and the JSON_VALUE, JSON_QUERY, and JSON_TABLE functions support the TYPE ({STRICT | LAX}) option, whose default is LAX. In these condition and functions, TYPE controls type compatibility rather than the grammar used to parse the document: lax type compatibility can convert a JSON string such as "42" to a number, while strict type compatibility does not attempt that conversion. This behavior is separate from strict or lax textual syntax.

Topics:

Strict JSON Syntax

Strict syntax follows the JSON standards, including IETF RFC 8259 and ECMA 404. In strict syntax:

Object field names and string values are enclosed in double quotation marks (").
The literals true, false, and null use lowercase spelling.
Numbers use the standard JSON form. For example, a number cannot have a leading plus sign (+), unnecessary leading zeros, or a missing digit before or after the decimal point.
Only JSON whitespace is insignificant outside quoted strings: space, tab, line feed, and carriage return.
Control characters and the characters that have special meaning in a string, such as backslash and double quotation mark, must be escaped.

This is the preferred syntax for JSON exchanged with other systems.

Example 1-2 A JSON Object with Strict JSON Syntax

This example shows a JSON object using strict JSON syntax.

{"name":"Ada","active":true,"score":42}

Lax JSON Syntax

Lax syntax is an input-parsing mode that accepts some JavaScript or ECMAScript 5.1 forms that are not accepted by strict JSON. Depending on the input, lax syntax can allow:

Unquoted object field names when the name does not contain whitespace, JSON structural characters, slash, backslash, or quotation marks.
Single quotation marks as string delimiters.
Case variations of true, false, and null, such as TRUE or Null.
One trailing comma after the last array element or object field.
More permissive numeric forms, such as leading zeros, .14, 342., or +1.3.
A broader set of Unicode whitespace characters and, in some cases, unescaped line and paragraph separator characters.

The JSON object in Example 1-3 is not strictly well-formed JSON. Lax syntax is useful when consuming JavaScript-oriented input, but strict JSON should be used for durable storage and interchange whenever possible.

Example 1-3 A JSON Object with Lax JSON Syntax

This example shows a JSON object using lax JSON syntax.

{name:'Ada', active:TRUE, score:+42,}

JSON Type Values and OSON

After textual JSON is converted to type JSON, the value is represented in OSON. Strict and lax are properties of textual JSON syntax and do not describe an OSON value. OSON also does not preserve duplicate object field names.

When a JSON value is converted back to text with JSON_SERIALIZE, TimesTen produces standard JSON text. The result uses strict JSON syntax, including standard JSON literals, valid number syntax, and required escaping. Oracle-specific scalar JSON values such as dates and timestamps are serialized into standard JSON representations, such as strings.

Differentiating Syntax from Type Compatibility

The words strict and lax are also used for type compatibility, which is a separate concept.

Strict syntax controls whether textual input follows the JSON grammar.
Lax syntax controls whether certain non-standard textual forms are accepted while parsing.
Strict type compatibility requires a JSON value to already have the expected JSON-language type.
Lax type compatibility can attempt a conversion to the expected type. For example, the JSON string "42" can be treated as the number 42 for a numeric comparison or extraction.

In the SQL/JSON functions, TYPE (STRICT) and TYPE (LAX) control this type-compatibility behavior. They do not turn a textual JSON document into a different storage format and should not be described as the same thing as strict or lax syntax checking. See TYPE Clause.

Example 1-4 Type-Compatibility Behavior

The expression in this example use the same textual document but request different type-compatibility behavior.

-- Lax type compatibility can convert the JSON string "42" to NUMBER.
SELECT JSON_VALUE('{"id":"42"}', '$.id' 
                  RETURNING NUMBER TYPE (LAX)) 
FROM dual;

-- Strict type compatibility does not make that conversion.
SELECT JSON_VALUE('{"id":"42"}', '$.id' 
                  RETURNING NUMBER TYPE (STRICT)) 
FROM dual;

The ON ERROR, ON EMPTY, and ON MISMATCH clauses determine what the function returns when parsing, path matching, or type conversion fails. Use an explicit error clause when an invalid document or conversion must be reported instead of being converted to a default NULL, FALSE, or other fallback result. See Clauses Used in SQL/JSON Functions and Conditions.

JSON_SCALAR Function

The JSON_SCALAR SQL/JSON function accepts a SQL scalar value as input and returns a corresponding JSON scalar value as a JSON type instance. The input value can be of an Oracle JSON-language type, such as a DATE—which is not part of the JSON standard.

When building JSON documents or fragments programmatically in SQL, JSON_SCALAR allows you to directly convert SQL scalar values into their corresponding JSON scalar representations. This is especially helpful when dealing with values that need to be treated as single, atomic JSON elements.

The argument to JSON_SCALAR can be an instance of any of these SQL data types: BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, JSON, NCHAR, NCLOB, NUMBER, NVARCHAR2, TIMESTAMP, or VARCHAR2.

The returned JSON type instance is a JSON-language scalar value supported by TimesTen. For example, JSON_SCALAR(SYSDATE) returns an Oracle JSON value of type DATE (as an instance of SQL data type JSON).

With JSON type input, JSON_SCALAR behaves as follows:

Input that corresponds to a JSON scalar value is simply returned.
Input that corresponds to a JSON non-scalar value results in an error. If the error handler is NULL ON ERROR, which it is by default, then SQL NULL (of JSON data type) is returned.

Table 1-1 JSON_SCALAR Type Conversion: SQL Types to Oracle JSON Types

SQL Type (Input)	JSON-Language Type (Output)
`BINARY_DOUBLE`	double
`BINARY_FLOAT`	float
`BLOB`	binary
`CHAR`	string
`CLOB`	string
`DATE`	date
`NCHAR`	string
`NCLOB`	string
`NUMBER`	number
`NVARCHAR2`	string
`TIMESTAMP`	timestamp
`VARCHAR2`	string

An exception are the numeric values of positive and negative infinity, and values that are the undefined result of a numeric operation ("not a number" or NaN)—they cannot be expressed as JSON numbers. For those, JSON_SCALAR returns not numeric-type values but the JSON strings "Inf", "-Inf", and "Nan", respectively.

A JSON type value returned by JSON_SCALAR remembers the SQL data type from which it was derived. If you then use JSON_VALUE (or a JSON_TABLE column with JSON_VALUE semantics) to extract that JSON type value, and you use the corresponding type-conversion item method, then the value extracted has the original SQL data type. For example, this query returns a SQL DATE value:

SELECT JSON_VALUE(JSON_SCALAR(sysdate), '$.date()') FROM dual;

Note that if the argument is a SQL string value (CHAR, CLOB, NCHAR, NCLOB, NVARCHAR2, or VARCHAR2), then JSON_SCALAR simply converts it to a JSON string value. The function does not parse the input as JSON data.

For example, JSON_SCALAR('{}') returns the JSON string value "{}". In contrast, the JSON constructor returns the empty JSON object {} for the same input. To produce the same JSON string using the JSON constructor, the double-quotation marks must be explicitly present in the input: JSON('"{}"').

If the argument to JSON_SCALAR is a SQL NULL value then you can obtain a return value as follows:

SQL NULL, the default behavior
JSON null, using keywords JSON NULL ON NULL

The default behavior of returning SQL NULL is the only exception to the rule that a JSON scalar value is returned.

See also:

JSON_SCALAR in Oracle TimesTen In-Memory Database SQL Reference

JSON_SERIALIZE Function

The JSON_SERIALIZE SQL/JSON function takes JSON data (of BLOB, CLOB, JSON, or VARCHAR2 SQL data type) as input and returns a textual representation of it (as CLOB or VARCHAR2 data). VARCHAR2 is the default return type.

You typically use JSON_SERIALIZE to transform the result of a query. The function supports an error clause and a returning clause. You can optionally do any combination of the following:

Automatically escape all non-ASCII Unicode characters, using standard ASCII Unicode escape sequences (keyword ASCII).
Pretty-print the result (keyword PRETTY).
Truncate the result to fit the return type (keyword TRUNCATE).

See Example 1-5 and Example 1-6.

By default, JSON_SERIALIZE always produces JSON data that conforms to the JSON standard (RFC 8259), in which case the returned data uses only the standard data types of the JSON language: object, array, string, number, Boolean, and null.

The stored JSON data that gets serialized can also have values of scalar types that Oracle has added to the JSON language. JSON data of such types is converted when serialized according to Table 1-2. For example, a numeric value of JSON-language type double is serialized by converting it to a textual representation of a JSON number.

Note:

Input JSON string values are returned verbatim. If you want to serialize a JSON non-string scalar value using a different format from what is specified in the table, first use a SQL conversion function—such as TO_CHAR—to produce the string value formatted as you want. Then, pass that value to JSON_SERIALIZE.

Table 1-2 JSON_SERIALIZE Converts Oracle JSON-Language Types To Standard JSON-Language Types

Scalar Oracle JSON Type	Standard JSON Type	Notes
binary	string	Binary bytes are converted to hexadecimal characters representing their values.
date	string	The string is in an ISO 8601 date format: YYYY-MM-DD. For example, `"2025-02-20"`.
double	number	Conversion is equivalent to the use of `TO_NUMBER` SQL function.
float	number	Conversion is equivalent to the use of `TO_NUMBER` SQL function.
timestamp	string	The string is in an ISO 8601 date-with-time format: YYYY-MM-DDThh:mm:ss.ssssss. For example, `"2025-02-20T10:04:02.340129"`.

You can use JSON_SERIALIZE to convert binary JSON data to textual form (CLOB or VARCHAR2), or to transform textual JSON data by pretty-printing it or escaping non-ASCII Unicode characters in it. A relevant use case is serializing JSON data that is stored in a JSON type column.

Note:

You can use the type() JSON path-expression item method to determine the JSON-language type of any JSON scalar value. It returns the type name as one of these JSON strings: binary, boolean, date, double, float, number, null, string, or timestamp.

See also:

JSON_SERIALIZE in Oracle TimesTen In-Memory Database SQL Reference

Example 1-5 Using JSON_SERIALIZE to Convert JSON Type to Pretty-Printed Text

This example serializes and pretty-prints the purchase order that has 1600 as value for the PONumber field name, which is selected from the po_document column (JSON type) of the j_purchaseorder table. The data type for the return value is the default, VARCHAR2.

SELECT JSON_SERIALIZE(po_document PRETTY)
  FROM j_purchaseorder po
  WHERE po.po_document.PONumber = 1600;

The query returns this output given the JSON data in inserted into the j_purchaseorder table in Example 2-2.

< {
  "PONumber" : 1600,
  "Reference" : "ABULL-20140421",
  "Requestor" : "Alexis Bull",
  "User" : "ABULL",
  "CostCenter" : "A50",
  "ShippingInstructions" :
  {
    "name" : "Alexis Bull",
    "Address" :
    {
      "street" : "200 Sporting Green",
      "city" : "South San Francisco",
      "state" : "CA",
      "zipCode" : 99236,
      "country" : "United States of America"
    },
    "Phone" :
    [
      {
        "type" : "Office",
        "number" : "909-555-7307"
      },
      {
        "type" : "Mobile",
        "number" : "415-555-1234"
      }
    ]
  },
  "Special Instructions" : null,
  "AllowPartialShipment" : true,
  "LineItems" :
  [
    {
      "ItemNumber" : 1,
      "Part" :
      {
        "Description" : "One Magic Christmas",
        "UnitPrice" : 19.95,
        "UPCCode" : 13131092899
      },
      "Quantity" : 9
    },
    {
      "ItemNumber" : 2,
      "Part" :
      {
        "Description" : "Lethal Weapon",
        "UnitPrice" : 19.95,
        "UPCCode" : 85391628927
      },
      "Quantity" : 5
    }
  ]
} >
1 row found.

Example 1-6 Using JSON_SERIALIZE to Convert Non-ASCII Unicode Characters to ASCII Escape Codes

This example serializes an object that has a string value with a non-ASCII character (€).

SELECT JSON_SERIALIZE('{"price" : 20, "currency" : "€"}' ASCII)
  FROM dual;

The query returns:

{"price":20,"currency":"\u20AC"}