JSON in TimesTen

TimesTen supports JSON data through the Oracle JSON library and the JSON data type, allowing you to store, query, and manipulate JSON data in a TimesTen database. JSON data is stored using Oracle Binary JSON (OSON) format, which provides benefits such as faster parsing, smaller storage requirements, and improved query performance.

TimesTen provides the JSON data type and the JSON_SCALAR and JSON_SERIALIZE SQL/JSON functions to work with JSON data, enabling you to convert between JSON and SQL data types, and to serialize JSON data to textual representation.

TimesTen also provides the JSON_VALUE, JSON_QUERY and JSON_TABLE SQL/JSON functions and the JSON_EQUAL and JSON_EXISTS conditions, enabling you to create, query, and operate on JSON data stored in a TimesTen database. See Query JSON Data in TimesTen.

Topics:

• About JSON in TimesTen

• JSON Data Type

• JSON Data Type Constructor

• JSON_SCALAR Function

• JSON_SERIALIZE Function

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

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-2 and Example 1-3.

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-2 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-3 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"}