1. JSON Developer's Guide
  2. JSON Data and Oracle Database
  3. JSON in Oracle Database

2 JSON in Oracle Database

Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views.

This documentation covers the use of database languages and features to work with JSON data that is stored in Oracle Database. In particular, it covers how to use SQL and PL/SQL with JSON data.

Note:

Oracle also provides a family of Simple Oracle Document Access (SODA) APIs for access to JSON data stored in the database. SODA is designed for schemaless application development without knowledge of relational database features or languages such as SQL and PL/SQL. It lets you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know how the documents are stored in the database.

There are several implementations of SODA:

  • SODA for REST — Representational state transfer (REST) requests perform collection and document operations, using any language capable of making HTTP calls.

  • SODA for Java — Java classes and interfaces represent databases, collections, and documents.

  • SODA for PL/SQL — PL/SQL object types represent collections and documents.

  • SODA for C — Oracle Call Interface (OCI) handles represent collections and documents.

  • SODA for Node.js — Node.js classes represent collections and documents.

  • SODA for Python — Python objects represent collections and documents.

For complete information about SODA see Simple Oracle Document Access (SODA).

  • Getting Started Using JSON with Oracle Database
    In general, you do the following when working with JSON data in Oracle Database: (1) create a table with a column of data type JSON, (2) insert JSON data into the column, and (3) query the data in the column.
  • Overview of JSON in Oracle Database
    Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views. Unlike relational data, JSON data can be stored in the database, indexed, and queried without any need for a schema that defines the data.
  • JSON Data Type, To and From
    SQL data type JSON represents JSON data using a native binary format, OSON, which is Oracle's optimized format for fast query and update in both Oracle Database server and Oracle Database clients. You can create JSON type instances from other SQL data, and conversely.
  • Oracle Database Support for JSON
    Oracle Database support for JavaScript Object Notation (JSON) is designed to provide the best fit between the worlds of relational storage and querying JSON data, allowing relational and JSON queries to work well together. Oracle SQL/JSON support is closely aligned with the JSON support in the SQL Standard.

Parent topic: JSON Data and Oracle Database

2.1 Getting Started Using JSON with Oracle Database

In general, you do the following when working with JSON data in Oracle Database: (1) create a table with a column of data type JSON, (2) insert JSON data into the column, and (3) query the data in the column.

  1. Create a table with a primary-key column and a column of JSON data type.

    The following statement creates table j_purchaseorder with primary key id and with JSON column po_document. 

    CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document JSON);

    You can alternatively insert JSON data into a column that has a data type other than JSON type, such as VARCHAR2. In that case, use an is json check constraint to ensure that the data inserted into the column is well-formed JSON data. See Example 4-2.

  2. Insert JSON data into the JSON column, using any of the methods available for Oracle Database.

    The following statement uses a SQL INSERT statement to insert some simple JSON data into the third column of table j_purchaseorder (which is column po_document — see previous). Some of the JSON data is elided here (...). 

    INSERT INTO j_purchaseorder
  VALUES (SYS_GUID(),
          to_date('30-DEC-2014'),
          '{"PONumber"             : 1600,
            "Reference"            : "ABULL-20140421",
            "Requestor"            : "Alexis Bull",
            "User"                 : "ABULL",
            "CostCenter"           : "A50",
            "ShippingInstructions" : {...},
            "Special Instructions" : null,
            "AllowPartialShipment" : true,
            "LineItems"            : [...]}');

    The SQL string '{"PONumber":1600,…}' is automatically converted to JSON data type for the INSERT operation.

  3. Query the JSON data. The return value is always a VARCHAR2 instance that represents a JSON value. Here are some simple examples.

    The following query extracts, from each document in JSON column po_document, a scalar value, the JSON number that is the value of field PONumber for the objects in JSON column po_document (see also Example 14-1): 

    SELECT po.po_document.PONumber FROM j_purchaseorder po;

    The following query extracts, from each document, an array of JSON phone objects, which is the value of field Phone of the object that is the value of field ShippingInstructions (see also Example 14-2): 

    SELECT po.po_document.ShippingInstructions.Phone
  FROM j_purchaseorder po;

    The following query extracts, from each document, multiple values as an array: the value of field type for each object in array Phone. The returned array is not part of the stored data but is constructed automatically by the query. (The order of the array elements is unspecified.) 

    SELECT po.po_document.ShippingInstructions.Phone.type
  FROM j_purchaseorder po;

Related Topics

Parent topic: JSON in Oracle Database

2.2 Overview of JSON in Oracle Database

Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views. Unlike relational data, JSON data can be stored in the database, indexed, and queried without any need for a schema that defines the data.

(The JSON data is schemaless, even though a database schema is used to define the table and column in which it is stored. Nothing in that schema specifies the structure of the JSON data itself.)

JSON data has often been stored in NoSQL databases such as Oracle NoSQL Database and Oracle Berkeley DB. These allow for storage and retrieval of data that is not based on any schema, but they do not offer the rigorous consistency models of relational databases.

To compensate for this shortcoming, a relational database is sometimes used in parallel with a NoSQL database. Applications using JSON data stored in the NoSQL database must then ensure data integrity themselves.

Native support for JSON by Oracle Database obviates such workarounds. It provides all of the benefits of relational database features for use with JSON, including transactions, indexing, declarative querying, and views.

Database queries with Structured Query Language (SQL) are declarative. With Oracle Database you can use SQL to join JSON data with relational data. And you can project JSON data relationally, making it available for relational processes and tools. You can also query, from within the database, JSON data that is stored outside Oracle Database in an external table.

You can access JSON data stored in the database the same way you access other database data, including using Oracle Call Interface (OCI), and Java Database Connectivity (JDBC).

With its native binary JSON format, OSON, Oracle extends the JSON language by adding scalar types, such as date and double, which are not part of the JSON standard.

  • Data Types for JSON Data
    SQL data type JSON is Oracle's binary JSON format for fast query and update. It extends the standard JSON scalar types (number, string, Boolean, and null), to include types that correspond to SQL scalar types. This makes conversion of scalar data between JSON and SQL simple and lossless.
  • JSON Columns in Database Tables
    Oracle Database places no restrictions on the tables that can be used to store JSON documents. A column containing JSON documents can coexist with any other kind of database data. A table can also have multiple columns that contain JSON documents.
  • Use SQL With JSON Data
    In SQL, you can create and access JSON data in Oracle Database using JSON data type constructor JSON, specialized functions and conditions, or a simple dot notation. Most of the SQL functions and conditions belong to the SQL/JSON standard, but a few are Oracle-specific.
  • Use PL/SQL With JSON Data
    You can use JSON data type instances as input and output of PL/SQL subprograms, and you can manipulate JSON data within PL/SQL code using SQL code or PL/SQL object types for JSON.

Related Topics

Parent topic: JSON in Oracle Database

2.2.1 Data Types for JSON Data

SQL data type JSON is Oracle's binary JSON format for fast query and update. It extends the standard JSON scalar types (number, string, Boolean, and null), to include types that correspond to SQL scalar types. This makes conversion of scalar data between JSON and SQL simple and lossless.

Standard JSON, as a language or notation, has predefined data types: object, array, number, string, Boolean, and null. All JSON-language types except object and array are scalar types.

The standard defines JSON data in a textual way: it is composed of Unicode characters in a standard syntax.

When actual JSON data is used in a programming language or is stored in some way, it is realized using a data type in that particular language or storage format. For example, a JDBC client application might fill a Java string with JSON data, or a database column might store JSON data using a SQL data type.

It's important to keep these two kinds of data type in mind. For example, though the JSON-language type of JSON value "abc" is string, this value can be represented, or realized, using a value of any of several SQL data types: JSON, VARCHAR2, CLOB, or BLOB.

SQL type JSON is designed specifically for JSON data. Oracle recommends that for use with Oracle Database you use JSON type for your JSON data. This uses a binary format, OSON, which is Oracle's optimized binary JSON format for fast query and update in both Oracle Database server and Oracle Database clients. JSON type is available only if database initialization parameter compatible is at least 20.

When you use one of the other SQL types for JSON data (VARCHAR2, CLOB, or BLOB), the data is said to be textual — it is unparsed character data (even when stored as a BLOB instance).

When JSON data is of SQL data type JSON, Oracle extends the set of standard JSON-language scalar types (number, string, Boolean, and null) to include several that correspond to SQL scalar types: binary, date, timestamp, year-month interval, day-second interval, double, and float. This enhances the JSON language, and it makes conversion of scalar data between that language and SQL simple and lossless.

When JSON data is of SQL data type VARCHAR2, CLOB, or BLOB, only the standard JSON-language scalar types are supported. But when JSON data is of SQL type JSON, Oracle Database extends the set of standard JSON-language types to include several scalar types that correspond directly to SQL scalar data types, as follows:

  • binary — Corresponds to SQL RAW.

  • date — Corresponds to SQL DATE.

  • timestamp — Corresponds to SQL TIMESTAMP.

  • year-month interval — Corresponds to SQL INTERVAL YEAR TO MONTH.

  • day-second interval — Corresponds to SQL INTERVAL DAY TO SECOND.

  • double — Corresponds to SQL BINARY_DOUBLE.

  • float — Corresponds to SQL BINARY_FLOAT.

Here are some ways to obtain JSON scalar values of such Oracle-specific JSON-language types in your JSON data that is stored as JSON type:

  • Use SQL/JSON generation functions with RETURNING JSON. Scalar SQL values used in generating array elements or object field values result in JSON scalar values of corresponding JSON-language types. For example, a BINARY_FLOAT SQL value results in a float JSON value.

  • Use Oracle SQL function json_scalar. For example, applying it to a BINARY_FLOAT SQL value results in a float JSON value.

  • Use a database client with client-side encoding to create an Oracle-specific JSON value as JSON type before sending that to the database.

  • Instantiate PL/SQL object types for JSON with JSON data having Oracle-specific JSON scalar types. This includes updating existing such object-type instances.

  • Use PL/SQL method to_json() on a PL/SQL DOM instance (JSON_ELEMENT_T instance).

Here are some ways to make use of JSON scalar values of Oracle-specific JSON-language types:

  • Use SQL/JSON condition json_exists, comparing the value of a SQL bind variable with the result of applying an item method that corresponds to an Oracle-specific JSON scalar type.

  • Use SQL/JSON function json_value with a RETURNING clause that returns a SQL type that corresponds to an Oracle-specific JSON scalar type.

Parent topic: Overview of JSON in Oracle Database

2.2.2 JSON Columns in Database Tables

Oracle Database places no restrictions on the tables that can be used to store JSON documents. A column containing JSON documents can coexist with any other kind of database data. A table can also have multiple columns that contain JSON documents.

When using Oracle Database as a JSON document store, your tables that contain JSON columns typically also have a few non-JSON housekeeping columns. These typically track metadata about the JSON documents.

If you use JSON data to add flexibility to a primarily relational application then some of your tables likely also have a column for JSON documents, which you use to manage the application data that does not map directly to your relational model.

Oracle recommends that you use data type JSON for JSON columns. If you instead use textual JSON storage (VARCHAR2, CLOB, or BLOB) then Oracle recommends that you use an is json check constraint to ensure that column values are valid JSON instances (see Example 4-2).

By definition, textual JSON data is encoded using a Unicode encoding, either UTF-8 or UTF-16. You can use VARCHAR2 or CLOB data that is stored in a non-Unicode character set as if it were JSON data, but in that case Oracle Database automatically converts the character set to UTF-8 when processing the data.

Data stored using data type JSON or BLOB is independent of character sets and does not undergo conversion when processing the data.

Parent topic: Overview of JSON in Oracle Database

2.2.3 Use SQL With JSON Data

In SQL, you can create and access JSON data in Oracle Database using JSON data type constructor JSON, specialized functions and conditions, or a simple dot notation. Most of the SQL functions and conditions belong to the SQL/JSON standard, but a few are Oracle-specific.

  • SQL/JSON query functions json_value, json_query, and json_table.

    These evaluate SQL/JSON path expressions against JSON data to produce SQL values.

  • Oracle SQL condition json_textcontains and SQL/JSON conditions json_exists, is json, and is not json.

    Condition json_exists checks for the existence of given JSON data; json_textcontains provides full-text querying of JSON data; and is json and is not json check whether given JSON data is well-formed.

    json_exists and json_textcontains check the data that matches a SQL/JSON path expression.

  • A simple dot notation that acts similar to a combination of query functions json_value and json_query.

    This resembles a SQL object access expression, that is, attribute dot notation for an abstract data type (ADT). This is the easiest way to query JSON data in the database.

  • SQL/JSON generation functions json_object, json_array, json_objectagg, and json_arrayagg.

    These gather SQL data to produce JSON object and array data (as a SQL value).

  • Oracle SQL functions json_serialize and json_scalar, and Oracle SQL condition json_equal.

    Function json_serialize returns a textual representation of JSON data; json_scalar returns a JSON type scalar value that corresponds to a given SQL scalar value; and json_equal tests whether two JSON values are the same.

  • JSON data type constructor JSON.

    This parses textual JSON data to create an instance of SQL data type JSON.

  • Oracle SQL aggregate function json_dataguide.

    This produces JSON data that is a data guide, which you can use to discover information about the structure and content of other JSON data in the database.

As a simple illustration of querying, here is a dot-notation query of the documents stored in JSON column po_document of table j_purchaseorder (aliased here as po). It obtains all purchase-order requestors (JSON field Requestor). 

SELECT po.po_document.Requestor FROM j_purchaseorder po;

Parent topic: Overview of JSON in Oracle Database

2.2.4 Use PL/SQL With JSON Data

You can use JSON data type instances as input and output of PL/SQL subprograms, and you can manipulate JSON data within PL/SQL code using SQL code or PL/SQL object types for JSON.

You can generally use SQL code, including SQL code that accesses JSON data, within PL/SQL code.

The following SQL functions and conditions are also available as built-in PL/SQL functions: json_value, json_query, json_object, json_array, json_scalar, json_serialize, json_exists, is json, is not json, and json_equal.

Unlike the case for Oracle SQL, which has no BOOLEAN data type, in PL/SQL:

  • json_exists, is json, is not json, and json_equal are Boolean functions.

  • json_value can return a BOOLEAN value.

  • json_scalar can accept a BOOLEAN value as argument, in which case it returns a Boolean JSON type instance (true or false).

There are also PL/SQL object types for JSON, which you can use for fine-grained construction and manipulation of In-Memory JSON data. You can introspect it, modify it, and serialize it back to textual JSON data.

You can use JSON data type instances as input and output of PL/SQL subprograms. You can manipulate such data in PL/SQL by instantiating JSON object types, such as JSON_OBJECT_T.

Parent topic: Overview of JSON in Oracle Database

2.3 JSON Data Type, To and From

SQL data type JSON represents JSON data using a native binary format, OSON, which is Oracle's optimized format for fast query and update in both Oracle Database server and Oracle Database clients. You can create JSON type instances from other SQL data, and conversely.

The other SQL data types that support JSON data, besides JSON type, are VARCHAR2, CLOB, and BLOB. This non-JSON type data is called textual, or serialized, JSON data. It is unparsed character data (even when stored as a BLOB instance, as the data is a sequence of UTF-8 encoded bytes).

Using data type JSON avoids costly parsing of textual JSON data and provides better query performance.

You can convert textual JSON data to JSON type data by parsing it with type constructor JSON. JSON text that you insert into a database column of type JSON is parsed implicitly — you need not use the constructor explicitly.

In the other direction, you can convert JSON type data to textual JSON data using SQL/JSON function json_serialize. JSON type data that you insert into a database column of a JSON textual data type (VARCHAR2, CLOB, or BLOB) is serialized implicitly — you need not use json_serialize explicitly.

Regardless of whether the JSON type data uses Oracle-specific scalar JSON types (such as date), the resulting serialized JSON data always conforms to the JSON standard.

You can create complex JSON type data from non-JSON type data using the SQL/JSON generation functions: json_object, json_array, json_objectagg, and json_arrayagg.

You can create a JSON type instance with a scalar JSON value using Oracle SQL function json_scalar. In particular, the value can be of an Oracle-specific JSON-language type, such as a date, which is not part of the JSON standard.

In the other direction, you can use SQL/JSON function json_value to query JSON type data and return an instance of a SQL object type or collection type.

JSON data type, its constructor JSON, and Oracle SQL function json_scalar can be used only if database initialization parameter compatible is at least 20. Otherwise, trying to use any of them raises an error.

Note:

You cannot compare instances of JSON data type directly using operators such as = and >. This implies that you cannot use them with ORDER BY or GROUP BY.

You can, however, use json_value or the simple dot-notation syntax, together with data type-conversion item methods, to extract SQL scalar values from a JSON type instance, and then use such comparison operators on the extracted values.

  • JSON Data Type Constructor
    The JSON data type constructor, JSON, takes as input a textual JSON value (a scalar, object, or array), parses it, and returns the value as an instance of JSON type.
  • Oracle SQL Function JSON_SCALAR
    Oracle SQL function json_scalar accepts a SQL scalar value as input and returns a corresponding JSON scalar value as a JSON type instance. In particular, the value can be of an Oracle-specific JSON-language type, such as a date, which is not part of the JSON standard.
  • Oracle SQL Function JSON_SERIALIZE
    Oracle SQL function json_serialize takes JSON data (of any SQL data type, JSON, VARCHAR2, CLOB, or BLOB) as input and returns a textual representation of it (as VARCHAR2, CLOB, or BLOB data). VARCHAR2(4000) is the default return type.
  • JSON Constructor, JSON_SCALAR, and JSON_SERIALIZE: Summary
    Relations among JSON data type constructor JSON, Oracle SQL function json_scalar, and Oracle SQL function json_serialize are summarized.
  • Objects That Extend JSON Scalars
    Native binary JSON data (OSON format) extends the JSON language by adding scalar types, such as date, that correspond to SQL types and are not part of the JSON standard. Oracle Database also supports the use of textual JSON objects that represent JSON scalar values, including such nonstandard values.
  • Migration of Textual JSON Data to JSON Type Data
    Oracle recommends that you store JSON data in the database using JSON data type. You can migrate existing data from textual JSON storage (VARCHAR2, CLOB, or BLOB) to JSON type storage using Oracle GoldenGate or online redefinition.

Related Topics

See Also:

Parent topic: JSON in Oracle Database

2.3.1 JSON Data Type Constructor

The JSON data type constructor, JSON, takes as input a textual JSON value (a scalar, object, or array), parses it, and returns the value as an instance of JSON type.

For example, given SQL string '{}' as input, the JSON type instance returned is the empty object {}. The input '{a : {"b":"beta", c:[+042, "gamma",]},}' results in the JSON instance {"a":{"b":"beta","c":[42,"gamma"]}}.

(Note that this contrasts with the behavior of Oracle SQL function json_scalar, which does not parse textual input but just converts it to a JSON string value: json_scalar('{}') returns the JSON string "{}". To produce the same JSON string using constructor JSON, you must add explicit double-quote characters: JSON('"{}"').)

You can use constructor JSON only if database initialization parameter compatible is at least 20. Otherwise, the constructor raises an error (regardless of what input you pass it).

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

The value returned by the constructor can be any JSON value that is supported by Oracle. This includes values of the standard JSON types: object, array, string, Boolean, null, and number. It also includes any non-standard Oracle scalar JSON values, that is, values of the Oracle-specific scalar types: double, float, binary, date, timestamp, day-second interval, and year-month interval. If the constructor is used with keyword EXTENDED then the values of the Oracle-specific types can be derived from Oracle extended-object patterns in the textual JSON input.

If the input is not well-formed JSON data then an error is raised. It can have lax JSON syntax, and any objects in it can have duplicate field (key) names. Other than this relaxation, to be well-formed the input data must conform to RFC 8259.

If the input has an object with duplicate field names then only one of the field values is used. If you need to ensure that the input uses only strict syntax or has only objects with unique field values then use SQL condition is json to filter it. This code prevents acceptance of non-strict syntax and objects with duplicate fields: 

SELECT JSON(jcol) FROM table
  WHERE jcol is json (STRICT WITH UNIQUE KEYS);

As a convenience, when using textual JSON data to perform an INSERT or UPDATE operation on a JSON type column, the textual data is implicitly wrapped with constructor JSON.

Use cases for constructor JSON include on-the-fly parsing and conversion of textual JSON data to JSON type. (An alternative is to use condition is json in a WHERE clause.) You can pass the constructor a bind variable with a string value or data from an external table, for instance.

As one example, you can use constructor JSON to ensure that textual data that is not stored in the database with an is json check constraint is well-formed. You can then use the simple dot-notation query syntax with the resulting JSON type data. (You cannot use the dot notation with data that is not known to be well-formed.) Example 2-1 illustrates this.

Example 2-1 Converting Textual JSON Data to JSON Type On the Fly

This example uses simple dot-notation syntax to select a field from some textual JSON data that is not known to the database to be well-formed. It converts the data to JSON type data, before selecting. Constructor JSON raises an error if its argument is not well-formed. (Note that dot-notation syntax requires the use of a table alias — j in this case.) 

WITH jtab AS
  (SELECT JSON(
     '{ "name" : "Alexis Bull",
        "Address": { "street" : "200 Sporting Green",
                     "city" : "South San Francisco",
                     "state" : "CA",
                     "zipCode" : 99236,
                     "country" : "United States of America" } }')
     AS jcol FROM DUAL)
  SELECT j.jcol.Address.city FROM jtab j;

Related Topics

See Also:

Oracle Database SQL Language Reference for information about constructor JSON

Parent topic: JSON Data Type, To and From

2.3.2 Oracle SQL Function JSON_SCALAR

Oracle SQL function json_scalar accepts a SQL scalar value as input and returns a corresponding JSON scalar value as a JSON type instance. In particular, the value can be of an Oracle-specific JSON-language type, such as a date, which is not part of the JSON standard.

You can use function json_scalar only if database initialization parameter compatible is at least 20. Otherwise it raises an error.

You can think of json_scalar as a scalar generation function. Unlike the SQL/JSON generation functions, which can return any SQL data type that supports JSON data, json_scalar always returns an instance of JSON type.

The argument to json_scalar can be an instance of any of these SQL data types: VARCHAR2, RAW, CLOB, BLOB, DATE, TIMESTAMP, INTERVAL YEAR TO MONTH. INTERVAL DAY TO SECOND, NUMBER, BINARY_DOUBLE, or BINARY_FLOAT.

The returned JSON type instance is a JSON-language scalar value supported by Oracle. For example, json_scalar(current_timestamp) returns an Oracle JSON value of type timestamp (as an instance of SQL data type JSON).

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

SQL Type (Source) JSON Language Type (Destination)
VARCHAR2 string
CLOB string
BLOB binary
RAW binary
NUMBER number (or string if infinite or undefined value)
BINARY_DOUBLE double (or string if infinite or undefined value)
BINARY_FLOAT float (or string if infinite or undefined value)
DATE date
TIMESTAMP timestamp
INTERVAL DAY TO SECOND daysecondInterval
INTERVAL YEAR TO MONTH yearmonthInterval

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 TIMESTAMP value: 

SELECT json_value(json_scalar(current_timestamp), '$.timestamp()')
  FROM DUAL;

Note that if the argument is a SQL string value (VARCHAR2 or CLOB) then json_scalar simply converts it to a JSON string value. It does not parse the input as JSON data.

For example, json_scalar('{}') returns the JSON string value "{}". Because constructor JSON parses a SQL string, it returns the empty JSON object {} for the same input. To produce the same JSON string using constructor JSON, the double-quote characters 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 of SQL NULL (the default behavior) or 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.)

Note:

Be aware that, although function json_scalar preserves timestamp values, it drops any time-zone information from a timestamp. The time-zone information is taken into account by converting to UTC time. See Table 2-3.

If you need to add explicit time-zone information as JSON data then record it separately from a SQL TIMESTAMP WITH TIME ZONE instance and pass that to a JSON generation function. Example 2-2 illustrates this.

Example 2-2 Adding Time Zone Information to JSON Data

This example inserts a TIMESTAMP WITH TIME ZONE value into a table, then uses generation function json_object to construct a JSON object. It uses SQL functions json_scalar and extract to provide the JSON timestamp and numeric time-zone inputs for json_object. 

CREATE TABLE t (tz TIMESTAMP WITH TIME ZONE);
  INSERT INTO t
    VALUES (to_timestamp_tz('2019-05-03 20:00:00 -8:30',
                            'YYYY-MM-DD HH24:MI:SS TZH:TZM'));

-- This query returns the UTC timestamp value "2019-05-04T04:30:00"
SELECT json_scalar(tz) FROM t;

-- Create a JSON object that has 3 fields:
--  timestamp:       JSON timestamp value (UTC time): 
--  timeZoneHours:   hours component of the time zone, as a JSON number
--  timeZoneMinutes: minutes component of the time zone, as a JSON number

SELECT json_object('timestamp'       : json_scalar(tz),
                   'timezoneHours'   : extract(TIMEZONE_HOUR FROM tz),
                   'timezoneMinutes' : extract(TIMEZONE_MINUTE FROM tz))
  FROM t;

-- That query returns a JSON object and prints it in serialized form.
-- The JSON timestamp value is serialized as an ISO 8601 date-time string.
-- The time-zone values (JSON numbers) are serialized as numbers.
--
-- {"timestamp"       : "2019-05-04T04:30:00",
--  "timezoneHours"   : -8,
--  "timezoneMinutes" : -30}

Related Topics

See Also:

Oracle Database SQL Language Reference for information about Oracle SQL function json_scalar

Parent topic: JSON Data Type, To and From

2.3.3 Oracle SQL Function JSON_SERIALIZE

Oracle SQL function json_serialize takes JSON data (of any SQL data type, JSON, VARCHAR2, CLOB, or BLOB) as input and returns a textual representation of it (as VARCHAR2, CLOB, or BLOB data). VARCHAR2(4000) 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).

  • Translate values of Oracle-specific scalar JSON-language types to Oracle extended-object patterns (keyword EXTENDED).

By default, function 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, and the scalar types 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 2-2. For example, a numeric value of JSON-language type double is serialized by converting it to a textual representation of a JSON number.

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

Oracle JSON Scalar Type (Reported by type()) Standard Type Notes
binary string

Conversion is equivalent to the use of SQL function rawtohex: 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: "2019-05-21".

daysecondInterval string

The string is in an ISO 8601 duration format that corresponds to a ds_iso_format specified for SQL function to_dsinterval.

PdDThHmMsS, where d, h, m, and s are digit sequences for the number of days, hours, minutes, and seconds, respectively. For example: "P0DT06H23M34S".

s can also be an integer-part digit sequence followed by a decimal point and a fractional-part digit sequence. For example: P1DT6H23M3.141593S.

Any sequence whose value would be zero is omitted, along with its designator. For example: "PT3M3.141593S". However, if all sequences would have zero values then the syntax is "P0D".
double number

Conversion is equivalent to the use of SQL function to_number.

float number

Conversion is equivalent to the use of SQL function to_number.

timestamp string

The string is in an ISO 8601 date-with-time format: YYYY-MM-DDThh:mm:ss.ssssss. For example: "2019-05-21T10:04:02.340129".

timestamp with time zone string The string is in an ISO 8601 date-with-time format: YYYY-MM-DDThh:mm:ss.ssssss(+|-)hh:mm or, for a zero offset from UTC, YYYY-MM-DDThh:mm:ss.ssssssZ For example: "2019-05-21T10:04:02.123000-08:00" or "2019-05-21T10:04:02.123000Z".
yearmonthInterval string

The string is in an ISO 8601 duration format that corresponds to a ym_iso_format specified for SQL function to_yminterval.

PyYmM, where y is a digit sequence for the number of years and m is a digit sequence for the number of months. For example: "P7Y8M".

If the number of years or months is zero then it and its designator are omitted. Examples: "P7Y", "P8M". However, if there are zero years and zero months then the syntax is "P0Y".

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. An important use case is serializing JSON data that is stored in a BLOB or JSON type column.

(You can use JSON data type only if database initialization parameter compatible is at least 20.)

A BLOB result is in the AL32UTF8 character set. But whatever the data type returned by json_serialize, the returned data represents textual JSON data.

Note:

You can use the JSON path-expression item method type() to determine the JSON-language type of any JSON scalar value.

It returns the type name as one of these JSON strings: "binary", "date", "timestamp", "timestamp with time zone", "yearmonthInterval", "daysecondInterval", "double", "float", "number", "null", "string". For example, if the targeted scalar JSON value is of type timestamp with time zone then type() returns the string "timestamp with time zone". See:

See Also:

  • JSON_SERIALIZE in Oracle Database SQL Language Reference for information about Oracle SQL function json_serialize

  • RAWTOHEX in Oracle Database SQL Language Reference for information about SQL function rawtohex

  • TO_NUMBER in Oracle Database SQL Language Reference for information about SQL function to_number

Example 2-3 Using JSON_SERIALIZE To Convert JSON type or BLOB Data To Pretty-Printed Text

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

Example 4-1 shows the creation of a table with a JSON type column. You can also use json_serialize to serialize BLOB data. 

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

Example 2-4 Using JSON_SERIALIZE To Convert Non-ASCII Unicode Characters to ASCII Escape Codes

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

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

The query returns {"currency" : "\u20AC", "price" : 20}.

Related Topics

Parent topic: JSON Data Type, To and From

2.3.4 JSON Constructor, JSON_SCALAR, and JSON_SERIALIZE: Summary

Relations among JSON data type constructor JSON, Oracle SQL function json_scalar, and Oracle SQL function json_serialize are summarized.

Both constructor JSON and function json_scalar accept an instance of a SQL type other than JSON and return an instance of JSON data type.

The constructor accepts only textual JSON data as input: a VARCHAR2, CLOB, or BLOB instance. It raises an error for any other input data type.

Function json_scalar accepts an instance of any of several scalar SQL types as input. For VARCHAR2 or CLOB input it always returns a JSON-language string, as an instance of JSON type.

The value returned by the constructor can be any JSON value that is supported by Oracle, including values of the Oracle-specific scalar types: double, float, binary, date, timestamp, day-second interval, and year-month interval. If the constructor is used with keyword EXTENDED then the values can be derived from Oracle extended-object patterns in the textual JSON input.

The JSON value returned by json_scalar is always a scalar — same JSON-language types as for the constructor, except for the non-scalar types (object and array). For example, an instance of SQL type DOUBLE as input results in a JSON type instance representing a value of (Oracle-specific) JSON-language type double.

When Oracle SQL function json_serialize is applied to a JSON type instance, any non-standard Oracle scalar JSON value is returned as a standard JSON scalar value. But if json_serialize is used with keyword EXTENDED then values of Oracle-specific scalar JSON-language types can be serialized to Oracle extended-object patterns in the textual JSON output.

Table 2-3 summarizes the effects of using constructor JSON and SQL function json_scalar for various SQL values as JSON data, producing JSON type instances, and the effect of serializing those instances. The constructor parses the input, which must be textual JSON data, or an error is raised. Function json_scalar converts its input SQL scalar value to a JSON-language scalar value. VARCHAR2 or CLOB input to json_scalar always results in a JSON string value (the input is not parsed as JSON data).

Except for the following facts, the result of serializing a value produced by the constructor is the same textual representation as was accepted by the constructor (but the textual SQL data type need not be the same, among VARCHAR2, CLOB, and BLOB):

  • The constructor accepts lax JSON syntax and json_serialize always returns strict syntax.

  • If any input JSON objects have duplicate field names then all but one of the field–value pairs is dropped by the constructor.

  • The order of field–value pairs in an object is not, in general, preserved: output order can differ from input order.

  • If the textual data to which the constructor is applied contains extended JSON constructs (JSON objects that specify non-standard scalar JSON values), then the resulting JSON type data can (with keyword EXTENDED) have some scalar values that result from translating those constructs to SQL scalar values. If json_serialize (with keyword EXTENDED) is applied to the resulting JSON type data then the result can include some extended JSON constructs that result from translating in the reverse direction.

    The translations in these two directions are not, in general, inverse operations, however. They are exact inverses only for Oracle, not non-Oracle, extended JSON constructs. Because extended JSON constructs are translated to Oracle-specific JSON scalar values in JSON type, their serialization back to textual JSON data as extended JSON objects can be lossy when they are originally of a non-Oracle format.

Table 2-3 Effect of Constructor JSON and Oracle SQL Function JSON_SCALAR: Examples

Input SQL Value SQL Type JSON Value from JSON Constructor JSON Scalar Value from JSON_SCALAR
{a:1} VARCHAR2

  • JSON object with field a and value 1

  • json_serialize result: {"a":1}

  • JSON string containing the text {"a":1}

  • json_serialize result: "{\"a\":1}" (escaped double-quote characters)

[1,2,3] VARCHAR2

  • JSON array with elements 1, 2, 3

  • json_serialize result: [1,2,3]

  • JSON string containing the text [1,2,3]

  • json_serialize result: "[1,2,3]"
true VARCHAR2

  • JSON Boolean value true

  • json_serialize result: true

  • JSON string containing the text true

  • json_serialize result: "true"
null VARCHAR2

  • JSON value null

  • json_serialize result: null

  • JSON string containing the text null

  • json_serialize result: "null"
SQL NULL VARCHAR2

  • SQL NULL (JSON type) — not JSON value null

  • json_serialize result: SQL NULL

  • SQL NULL (JSON type) — not JSON value null

  • json_serialize result: SQL NULL
"city" VARCHAR2

  • JSON string containing the text city

  • json_serialize result: "city"

  • JSON string containing the text "city" (including double-quote characters)

  • json_serialize result: "\"city\"" (escaped double-quote characters)

city VARCHAR2

Error — input is not valid JSON data (there is no JSON scalar value city)

  • JSON string containing the text city

  • json_serialize result: "city"
{"$numberDouble" : "1E300"} or {"$numberDouble" : 1E300}

(An extended JSON object.)

VARCHAR2

JSON scalar of type double

A JSON string with the same content as the input VARCHAR2 value

{"$numberDecimal" : "1E300"} or {"$numberDecimal" : 1E300}

(An extended JSON object.)

VARCHAR2

JSON scalar of type number, tagged internally as having been derived from a $numberDecimal extended object

A JSON string with the same content as the input VARCHAR2 value

{"$oid" : "deadbeefcafe0123456789ab"} or {"$rawid" : "deadbeefcafe0123456789ab"}

(An extended JSON object.)

VARCHAR2

JSON scalar of type binary, tagged internally as having been derived from a $rawid or $oid extended object

A JSON string with the same content as the input VARCHAR2 value

{"$date" : "2020-11-24T12:34:56"} or {"$oracleDate" : "2020-11-24T12:34:56"}

(An extended JSON object.)

VARCHAR2

JSON scalar of type date, tagged internally as having been derived from an $oracleDate or $date extended object

A JSON string with the same content as the input VARCHAR2 value

3.14 VARCHAR2

  • JSON number 3.14

  • json_serialize result: 3.14

  • JSON string containing the text 3.14

  • json_serialize result: "3.14"
3.14 NUMBER

Error — not textual JSON data (SQL types other than VARCHAR2, CLOB, and BLOB are not supported)

  • JSON number value 3.14

  • json_serialize result: 3.14
3.14 BINARY_DOUBLE

Error — not textual JSON data (SQL types other than VARCHAR2, CLOB, and BLOB are not supported)

  • JSON double value 3.14 (Oracle JSON language extension)

  • json_serialize result: 3.14
3.14 NUMBER, tagged internally as having been derived from a $numberDecimal extended object

JSON scalar of type number, tagged internally as having been derived from a $numberDecimal extended object

A JSON string with the same content as the original extended object
A RAW value RAW, tagged internally as having been derived from a $rawid or $oid extended object

JSON scalar of type binary, tagged internally as having been derived from a $rawid or $oid extended object

A JSON string with the same content as the original extended object
SQL date value from evaluating to_date('20.07.1974') DATE

Error — not textual JSON data

  • JSON date value (Oracle JSON language extension)

  • json_serialize result: ISO 8601 string "1974-07-20T00:00:00" (UTC date — input format is ignored)

SQL timestamp value from evaluating to_timestamp('2019-05-23 11:31:04.123', 'YYYY-MM-DD HH24:MI:SS.FF') TIMESTAMP

Error — not textual JSON data

  • JSON timestamp value (Oracle JSON language extension)

  • json_serialize result: ISO 8601 string "2019-05-23T11:31:04.123000"
SQL timestamp value from evaluating to_timestamp_tz('2019-05-23 11:31:04.123 -8', 'YYYY-MM-DD HH24:MI:SS.FF TZH') TIMESTAMP WITH TIMEZONE

Error — not textual JSON data

  • JSON timestamp with time zone value (Oracle JSON language extension)

  • json_serialize result: ISO 8601 string "2019-05-23T11:31.03.123000-08:00"

Related Topics

See Also:

Parent topic: JSON Data Type, To and From

2.3.5 Objects That Extend JSON Scalars

Native binary JSON data (OSON format) extends the JSON language by adding scalar types, such as date, that correspond to SQL types and are not part of the JSON standard. Oracle Database also supports the use of textual JSON objects that represent JSON scalar values, including such nonstandard values.

When you create native binary JSON data from textual JSON data that contains such extended objects, they can optionally be replaced with corresponding (native binary) JSON scalar values.

An example of an extended object is {"$numberDecimal":31}. It represents a JSON scalar value of the nonstandard type decimal number, and when interpreted as such it is replaced by a decimal number in native binary format.

For example, when you use the JSON data type constructor, JSON, if you use keyword EXTENDED then recognized extended objects in the textual input are replaced with corresponding scalar values in the native binary JSON result. If you do not include keyword EXTENDED then no such replacement occurs; the textual extended JSON objects are simply converted as-is to JSON objects in the native binary format.

In the opposite direction, when you use Oracle SQL function json_serialize to serialize binary JSON data as textual JSON data (VARCHAR2, CLOB, or BLOB), you can use keyword EXTENDED to replace (native binary) JSON scalar values with corresponding textual extended JSON objects.

Note:

If the database you use is an Oracle Autonomous Database then you can use PL/SQL procedure DBMS_CLOUD.copy_collection to create a JSON document collection from a file of JSON data such as that produced by common NoSQL databases, including Oracle NoSQL Database.

If you use ejson as the value of the type parameter of the procedure, then recognized extended JSON objects in the input file are replaced with corresponding scalar values in the resulting native binary JSON collection. In the other direction, you can use function json_serialize with keyword EXTENDED to replace scalar values with extended JSON objects in the resulting textual JSON data.

These are the two main use cases for extended objects:

  • Exchange (import/export):

    • Ingest existing JSON data (from somewhere) that contains extended objects.

    • Serialize native binary JSON data as textual JSON data with extended objects, for some use outside the database.

  • Inspection of native binary JSON data: see what you have by looking at corresponding extended objects.

For exchange purposes, you can ingest JSON data from a file produced by common NoSQL databases, including Oracle NoSQL Database, converting extended objects to native binary JSON scalars. In the other direction, you can export native binary JSON data as textual data, replacing Oracle-specific scalar JSON values with corresponding textual extended JSON objects.

As an example of inspection, consider an object such as {"dob" : "2000-01-02T00:00:00"} as the result of serializing native JSON data. Is "2000-01-02T00:00:00" the result of serializing a native binary value of type date, or is the native binary value just a string? Using json_serialize with keyword EXTENDED lets you know.

The mapping of extended object fields to scalar JSON types is, in general, many-to-one: more than one kind of extended JSON object can be mapped to a given scalar value. For example, the extended JSON objects {"$numberDecimal":"31"} and {"$numberLong:"31"} are both translated as the value 31 of JSON-language scalar type number, and item method type() returns number for each of those JSON scalars.

Item method type() reports the JSON-language scalar type of its targeted value. Some scalar values are distinguishable internally, even when they have the same scalar type. This generally allows function json_serialize (with keyword EXTENDED) to reconstruct the original extended JSON object. They are distinguished internally either by using different SQL types to implement them or by tagging them with the kind of extended JSON object from which they were derived.

When json_serialize reconstructs the original extended JSON object the result is not always textually identical to the original, but it is always semantically equivalent. For example, {"$numberDecimal":"31"} and {"$numberDecimal":31} are semantically equivalent, even though the field values differ in type (string and number). They are translated to the same internal value, and each is tagged as being derived from a $numberDecimal extended object (same tag). But when serialized, the result for both is {"$numberDecimal":31}. Oracle always uses the most directly relevant type for the field value, which in this case is the JSON-language value 31, of scalar type number.

Note:

There are two cases where the type of the original extended object can be lost when deriving the internal binary-JSON value.

  • An extended object with field $numberInt is translated to an Oracle SQL NUMBER internal value, with no tag. Serializing that value produces a standard JSON-language value of type number. There is no loss in the numerical value; the only loss is the information that the original textual data was a $numberInt extended object.

  • Use of field $numberDecimal with infinite, very small, very large, or not-a-number values is unsupported, and results in undefined behavior. Do not use a string value that represents positive infinity ("Infinity" or "Inf"), negative infinity ("-Infinity" or "-Inf"), or an unknown value (not a number, "Nan") with $numberDecimal — instead, use $numberDouble with such values.

You can generally go back and forth between native binary JSON data and textual JSON data without loss of information. However, comparison (and hence indexing) of data in SQL requires that you stay within the same type family.

You can use item method type() to identify the type family of a JSON value (but not the exact type within a family), which makes it useful for purposes of comparison or indexing.

You can compare JSON values only within each of the following type families.

  • Floating-point number types: double and float (from extended objects with $numberDouble or $numberFloat).

    Item method type() reports values in this family as double or float.

  • Decimal number types (from extended objects with $numberInt, $numberDecimal, or $numberLong).

    Item method type() reports values in this family as number.

  • Binary types, including identifiers (from extended objects with $binary, $oid, $rawhex or $rawid).

    Item method type() reports values in this family as binary.

  • Date and time point types (from extended objects with $date, $oracleDate, $oracleTimestamp or $oracleTimestampTZ).

    Item method type() reports values in this family as date or timestamp. It reports a timestamp-with-timezone value (from extended objects with $oracleTimestampTZ) as timestamp.

    A $date field has a timestamp-with-timezone value, because it allows fractional seconds, and the value is given for Coordinated Universal Time (UTC).

  • Date and time interval types (from extended objects with $intervalDaySecond or $intervalYearMonth).

    Item method type() reports values in this family as daysecondInterval or yearmonthInterval.

  • JSON string type

    Item method type() reports values in this family as string.

  • JSON null type

    Item method type() reports values in this family as null.

  • JSON Boolean type

    Item method type() reports values in this family as boolean.

Table 2-4 presents correspondences among the various types used. It maps across types of extended objects used as input, types reported by item method type(), SQL types used internally, standard JSON-language types used as output by function json_serialize, and types of extended objects output by json_serialize when keyword EXTENDED is specified.

Table 2-4 Extended JSON Object Type Relations

Extended Object Type (Input) Oracle JSON Scalar Type (Reported by type()) SQL Scalar Type Standard JSON Scalar Type (Output) Extended Object Type (Output)
$numberDouble with value a JSON number, a string representing the number, or one of these strings: "Infinity", "-Infinity", "Inf", "-Inf", "Nan"Foot 1 double BINARY_DOUBLE

number

 $numberDouble with value a JSON number or one of these strings: "Inf", "-Inf", "Nan"Foot 2
$numberFloat with value the same as for $numberDouble float BINARY_FLOAT

number

 $numberFloat with value the same as for $numberDouble
$numberDecimal with value the same as for $numberDouble number NUMBER

number

 $numberDecimal with value the same as for $numberDouble
$numberInt with value a signed 32-bit integer or a string representing the number number NUMBER

number

 $numberInt with value the same as for $numberDouble
$numberLong with value a JSON number or a string representing the number number NUMBER

number

 $numberLong with value the same as for $numberDouble

$binary with value one of these:

  • a string of base-64 characters
  • An object with fields base64 and subType, whose values are a string of base-64 characters and the number 0 (arbitrary binary) or 4 (UUID), respectively

When the value is a string of base-64 characters, the extended object can also have field $subtype with value 0 or 4, expressed as a one-byte integer (0-255) or a 2-character hexadecimal string. representing such an integer

binary

 BLOB or RAW

string

Conversion is equivalent to the use of SQL function rawtohex.

One of the following:
  • $binary with value a string of base-64 characters
  • $rawid with value a string of 32 hexadecimal characters, if input had a subType value of 4 (UUID)
$oid with value a string of 24 hexadecimal characters binary RAW(12)

string

Conversion is equivalent to the use of SQL function rawtohex.

$rawid with value a string of 24 hexadecimal characters
$rawhex with value a string with an even number of hexadecimal characters binary RAW

string

Conversion is equivalent to the use of SQL function rawtohex.

$binary with value a string of base-64 characters, right-padded with = characters
$rawid with value a string of 24 or 32 hexadecimal characters binary RAW

string

Conversion is equivalent to the use of SQL function rawtohex.

$rawid
$oracleDatewith value an ISO 8601 date string date DATE

string

 $oracleDatewith value an ISO 8601 date string
$oracleTimestamp with value an ISO 8601 timestamp string timestamp TIMESTAMP

string

 $oracleTimestamp with value an ISO 8601 timestamp string
$oracleTimestampTZ with value an ISO 8601 timestamp string with a numeric time zone offset or with Z timestamp TIMESTAMP WITH TIME ZONE

string

 $oracleTimestampTZ with value an ISO 8601 timestamp string with a numeric time zone offset or with Z

$date with value one of the following:

  • An integer millisecond count since January 1, 1990
  • An ISO 8601 timestamp string
  • An object with field numberLong with value an integer millisecond count since January 1, 1990
timestamp TIMESTAMP WITH TIME ZONE

string

 $oracleTimestampTZ with value an ISO 8601 timestamp string with a numeric time zone offset or with Z
$intervalDaySecond with value an ISO 8601 interval string as specified for SQL function to_dsinterval daysecondInterval INTERVAL DAY TO SECOND

string

 $intervalDaySecond with value an ISO 8601 interval string as specified for SQL function to_dsinterval
$intervalYearMonth with value an ISO 8601 interval string as specified for SQL function to_yminterval yearmonthInterval INTERVAL YEAR TO MONTH

string

 $intervalYearMonth with value an ISO 8601 interval string as specified for SQL function to_yminterval

Footnote 1 The string values are interpreted case-insensitively. For example, "NAN" "nan", and "nAn" are accepted and equivalent, and similarly "INF", "inFinity", and "iNf". Infinitely large ("Infinity" or "Inf") and small ("-Infinity" or "-Inf") numbers are accepted with either the full word or the abbreviation.

Footnote 2 On output, only these string values are used — no full-word Infinity or letter-case variants.

Related Topics

See Also:

IEEE Standard for Floating-Point Arithmetic (IEEE 754)

Parent topic: JSON Data Type, To and From

2.3.6 Migration of Textual JSON Data to JSON Type Data

Oracle recommends that you store JSON data in the database using JSON data type. You can migrate existing data from textual JSON storage (VARCHAR2, CLOB, or BLOB) to JSON type storage using Oracle GoldenGate or online redefinition.

When performing online redefinition, for the col_mapping input parameter to PL/SQL procedure DBMS_REDEFINITION.start_redef_table, you just specify constructor JSON as the mapping function.

For example, if text_jcol is the source column of textual JSON data, and json_type_col is the destination column of JSON data type, then you specify parameter col_mapping like this: 

BEGIN
  DBMS_REDEFINITION.start_redef_table(
    ...
    col_mapping => 'JSON(text_jcol) json_type_col');
END;

See Also:

Parent topic: JSON Data Type, To and From

2.4 Oracle Database Support for JSON

Oracle Database support for JavaScript Object Notation (JSON) is designed to provide the best fit between the worlds of relational storage and querying JSON data, allowing relational and JSON queries to work well together. Oracle SQL/JSON support is closely aligned with the JSON support in the SQL Standard.

  • Support for RFC 8259: JSON Scalars
    Starting with Release 21c, Oracle Database can support IETF RFC 8259, which allows a JSON document to contain only a JSON scalar value at top level. This support also means that functions that return JSON data can return scalar JSON values.

See Also:

Parent topic: JSON in Oracle Database

2.4.1 Support for RFC 8259: JSON Scalars

Starting with Release 21c, Oracle Database can support IETF RFC 8259, which allows a JSON document to contain only a JSON scalar value at top level. This support also means that functions that return JSON data can return scalar JSON values.

For this support, database initialization parameter compatible must be 20 or greater.

In database releases prior to 21c only IETF RFC 4627 was supported. It allows only a JSON object or array, not a scalar, at the top level of a JSON document. RFC 8259 support includes RFC 4627 support (and RFC 7159 support).

If parameter compatible is 20 or greater then JSON data, regardless of how it is stored, supports RFC 8259 by default. But for a given JSON column you can use an is json check constraint to exclude the insertion of documents there that have top-level JSON scalars (that is, support only RFC 4627, not RFC 8259), by specifying the new is json keywords DISALLOW SCALARS.

With parameter compatible 20 or greater you can also use keywords DISALLOW SCALARS with SQL/JSON function json_query (or with a json_table column that has json_query semantics) to specify that the return value must be a JSON object or array. Without these keywords a JSON scalar can be returned.

If parameter compatible is 20 or greater you can also use SQL data type JSON, its constructor JSON, and Oracle SQL function json_scalar. If compatible is less than 20 then an error is raised when you try to use them.

If compatible is 20 or greater you can nevertheless restrict some JSON data to not allow top-level scalars, by using keywords DISALLOW SCALARS. For example, you can use an is json check constraint with DISALLOW SCALARS to prevent the insertion of documents that have a top-level scalar JSON value.

WARNING:

If you change the value of parameter compatible to 20 or greater then you cannot later return it to a lower value.

Parent topic: Oracle Database Support for JSON