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

2.2 JSON Data Type

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 SQL/JSON 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 SQL/JSON 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.

  • 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. Alternatively, the input can be an instance of SQL type VECTOR, a user-defined PL/SQL type, or a SQL aggregate type.
  • SQL/JSON Function JSON_SCALAR
    SQL/JSON function json_scalar accepts a SQL scalar value as input and returns a corresponding JSON scalar value as a JSON type instance. The value can be of an Oracle-specific JSON-language type (such as a date), which is not part of the JSON standard.
  • SQL/JSON Function JSON_SERIALIZE
    SQL/JSON function json_serialize takes JSON data (of SQL data type BLOB, CLOB, JSON, VARCHAR2) as input and returns a textual representation of it (as BLOB or VARCHAR2 data). VARCHAR2(4000) is the default return type.
  • JSON Constructor, JSON_SCALAR, and JSON_SERIALIZE: Summary
    Relations among JSON data type constructor JSON, SQL/JSON function json_scalar, and SQL/JSON function json_serialize are summarized.
  • Textual JSON Objects That Represent Extended Scalar Values
    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.
  • Comparison and Sorting of JSON Data Type Values
    The canonical sort order for values of SQL data type JSON is described. It is used to compare all JSON values.
  • Comparison of SQL Values With JSON Data Type Values
    When comparing a JSON-type value with a SQL value of a type other than JSON, the same rules apply as when comparing two JSON-type values of the same family (e.g. numeric values), provided that the SQL type corresponds to one of the JSON-language types of that family.

Related Topics

See Also:

Parent topic: JSON in Oracle Database

2.2.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. Alternatively, the input can be an instance of SQL type VECTOR, a user-defined PL/SQL type, or a SQL aggregate type.

Note:

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).

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 SQL/JSON 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('"{}"').)

Textual 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.

Non-textual input to the constructor can be an instance of type VECTOR or any of the following user-defined data types:

  • PL/SQL Varray

  • PL/SQL record

  • SQL object type

  • PL/SQL index by binary_integer collection (IBBI)

  • PL/SQL nested table

  • PL/SQL associative array

A VECTOR instance as argument results in an Oracle JSON scalar value of type vector.

A varray instance as argument results in a JSON array. The JSON-array elements are created from the elements of the varray collection (in order).

Each of the other instances results in a JSON object. The JSON-object members are created from the attributes of a record instance or a SQL object instance, the indexes of an IBBI or nested-table instance, and the key–value pairs of an associative-array instance.

The value returned by the constructor can be any JSON value that is supported by Oracle. This includes values of the standard JSON-language 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: binary, date, day-second interval, double, float, timestamp, timestamp with time zone, vector, 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 textual input is not well-formed JSON data then an error is raised. This includes the case where it has one or more objects in it that have duplicate field (key) names. It can, however, have lax JSON syntax. Other than this syntax relaxation, to be well-formed the input data must conform to RFC 8259.

If you need to ensure that the textual input uses only strict JSON syntax then use SQL condition is json to filter it. This code prevents acceptance of non-strict syntax: 

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

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:

  • JSON Type Constructor in Oracle Database SQL Language Reference for information about constructor JSON

  • PL/SQL and JSON Type Conversions in Oracle Database PL/SQL Language Reference for information about the conversion of a non-textual argument to a JSON-type value

Parent topic: JSON Data Type

2.2.2 SQL/JSON Function JSON_SCALAR

SQL/JSON function json_scalar accepts a SQL scalar value as input and returns a corresponding JSON scalar value as a JSON type instance. 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: BINARY_DOUBLE, BINARY_FLOAT, BLOB, BOOLEAN, CHAR, CLOB, DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, JSON, NCHAR, NCLOB, NUMBER, NVARCHAR2, RAW, TIMESTAMP, TIMESTAMP WITH TIME ZONE, VARCHAR, VARCHAR2, or VECTOR.

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).

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 nonscalar 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.

Tip:

Because json_scalar returns NULL by default for nonscalar input, and because comparison involving a nonscalar JSON value can be more costly than scalar-with-scalar comparison, a simple manual optimization when ordering or comparing JSON data is to do so after wrapping it with json_scalar, thus effectively pruning nonscalars from the data to be compared. (More precisely, they are replaced with NULL, which is quickly compared.)

For example instead of this:

SELECT data FROM customers c
  ORDER BY c.data.revenue;

Use this:

SELECT data FROM customers c
  ORDER BY json_scalar(c.data.revenue);

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", "boolean", "date", "daysecondInterval", "double", "float", "number", "null", "string", "timestamp", "timestamp with time zone", "vector", "yearmonthInterval". 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:

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

SQL Type (Source) JSON Language Type (Destination)
VARCHAR2, VARCHAR, NVARCHAR2, CHAR, or NCHAR string
CLOB or NCLOB string
BLOB binary
RAW binary
BOOLEAN boolean
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
TIMESTAMP WITH TIME ZONE timestamp with time zone
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. VARCHAR, NVARCHAR, CHAR, NCHAR, 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 as follows:

  • SQL NULL, the default behavior

  • JSON null, using keywords JSON NULL ON NULL (keyword JSON is optional)

  • An empty JSON string, "", using keywords EMPTY STRING 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-4.

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:

  • JSON_SCALAR in Oracle Database SQL Language Reference for information about SQL/JSON function json_scalar

  • JSON Data Type in Oracle Database SQL Language Reference

  • Character Data Types in Oracle Database SQL Language Reference for information about SQL data types CHAR, NCHAR, VARCHAR2, VARCHAR, and NVARCHAR2

  • Large Object (LOB) Data Types in Oracle Database SQL Language Reference for information about SQL data types BLOB, CLOB, and NCLOB

  • Numeric Data Types in Oracle Database SQL Language Reference for information about SQL data types NUMBER, BINARY_DOUBLE, and BINARY_FLOAT

  • Datetime and Interval Data Types in Oracle Database SQL Language Reference for information about SQL data types DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND

  • Boolean Data Type in Oracle Database SQL Language Reference

  • RAW and LONG RAW Data Types in Oracle Database SQL Language Reference for information about SQL data type RAW

Parent topic: JSON Data Type

2.2.3 SQL/JSON Function JSON_SERIALIZE

SQL/JSON function json_serialize takes JSON data (of SQL data type BLOB, CLOB, JSON, VARCHAR2) as input and returns a textual representation of it (as BLOB or VARCHAR2 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:Foot 1

  • Automatically escape all non-ASCII Unicode characters, using standard ASCII Unicode escape sequences (keyword ASCII).

  • Pretty-print the result (keyword PRETTY).

  • Order the members of objects in the result — ascending alphabetical order by field name (keyword ORDERED).

    The order is defined by the VARCHAR2 collation with binary ordering as represented in the AL32UTF8 character set. Put differently, characters are ordered according to their Unicode code points.

  • 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) — see Textual JSON Objects That Represent Extended Scalar Values.

See Example 2-3 and Example 2-4.

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-3. 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 (no change). If you want to serialize a nonstring scalar JSON value using a different format from what is specified here, then first use a SQL conversion function such as to_char to produce the string value formatted as you want, and pass that value to json_serialize.

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

Oracle JSON Scalar Type (Reported by type()) Standard JSON 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".
vector array The elements of the JSON array are JSON numbers. They are converted from the SQL numbers in the vector.
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", "boolean", "date", "daysecondInterval", "double", "float", "number", "null", "string", "timestamp", "timestamp with time zone", "vector", "yearmonthInterval". 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:

Note:

You can serialize a SQL VECTOR instance to a textual JSON array of numbers using SQL function vector_serialize. (Function json_serialize serializes only JSON data. See vector_serialize in Oracle Database SQL Language Reference.)

See Also:

  • JSON_SERIALIZE in Oracle Database SQL Language Reference for information about SQL/JSON 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 with Ordered Object Members

This example serializes, orders object members, 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 ORDERED)
  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 (€). It also orders the fields alphabetically.

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

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

Related Topics

Parent topic: JSON Data Type

2.2.4 JSON Constructor, JSON_SCALAR, and JSON_SERIALIZE: Summary

Relations among JSON data type constructor JSON, SQL/JSON function json_scalar, and SQL/JSON 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 (1) textual JSON data as input: a VARCHAR2, CLOB, or BLOB instance or (2) a VECTOR type 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: binary, date, day-second interval, double, float, timestamp, timestamp with time zone, vector, 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 nonscalar 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 SQL/JSON 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. (You can also apply json_serialize to a VECTOR type instance, in which case it returns a textual JSON array of numbers.)

Table 2-4 summarizes the effects of using constructor JSON and SQL function json_scalar for various SQL values as JSON data, producing JSON type instances, and it shows the effect of serializing those instances.

The constructor parses the input, which must be textual JSON data (or else 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-4 Effect of Constructor JSON and SQL/JSON 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 (case-insensitive) BOOLEAN

  • JSON Boolean value true

  • json_serialize result: true

Same as JSON constructor.

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"
NULLFoot 2 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"
VECTOR instance VECTOR JSON scalar of type vector JSON scalar of type vector

Footnote 2 This is the SQL NULL value for type VARCHAR2, not a SQL string with characters NULL.

Related Topics

See Also:

Parent topic: JSON Data Type

2.2.5 Textual JSON Objects That Represent Extended Scalar Values

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 SQL/JSON 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.

Tip:

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 (as a JSON string). 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. Such scalar values 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.

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

Table 2-5 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 3 double BINARY_DOUBLE

number

 $numberDouble with value a JSON number or one of these strings: "Inf", "-Inf", "Nan"Foot 4
$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
$oracleDate with value an ISO 8601 date string date DATE

string

 $oracleDate with 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 with time zone 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 with time zone 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

Two fields:

  • Field $vector with value an array whose elements are numbers or the strings "Nan", "Inf", and "-Inf" (representing not-a-number and infinite values).

  • Field $vectorElementType with string value either"float32" or "float64". These correspond to IEEE 32-bit and IEEE 64-bit numbers, respectively.

vector VECTOR

array of numbers

Two fields:

  • Field $vector with value an array whose elements are numbers or the strings "Nan", "Inf", and "-Inf" (representing not-a-number and infinite values).

  • Field $vectorElementType with string value either"float32" or "float64".

Footnote 3 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 4 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

2.2.6 Comparison and Sorting of JSON Data Type Values

The canonical sort order for values of SQL data type JSON is described. It is used to compare all JSON values.

You can directly compare or sort values of JSON data type of any kind — whether scalar, object, or array. This means you can use JSON type directly in a WHERE clause, an ORDER BY clause, or a GROUP BY clause. The canonical sort order is defined across all JSON-type values, including scalar values derived from Oracle extended-object patterns in textual JSON input.

Note:

A JSON-language scalar value of type vector is compared or sorted by first converting it to a JSON array of numbers; the resulting array is the value that is then compared or sorted.

Note:

When comparing values of JSON data type in SQL, the size of the values being compared, as encoded for SQL comparison, must be less than 32K bytes. Otherwise, an error is raised. In practice, this SQL encoded-for-comparison size is roughly the size of a textual representation of the same JSON data.

For example, in this query the encoded sizes of fields dept and name must each be less than 32K: 

SELECT * 
  FROM emp t
  WHERE t.data.dept = 'SALES' ORDER BY t.data.name

This limit applies to SQL clauses ORDER BY and GROUP BY, as well as to the use of SQL-value comparison operators (such as > in a WHERE clause).

More precisely, the limit applies only to comparison and sorting done by SQL itself. It does not apply to comparison or sorting done within the JSON language. That is, there's no size limit for comparison or sorting done by a SQL operator for JSON, such as json_transform or json_exists. In particular, the limit doesn't apply to comparisons made in SQL/JSON path expressions.

The JSON-type sort (comparison) order is as follows.Foot 5

  • A scalar value sorts before a nonscalar value (after, with keyword DESC).

  • An object sorts before an array (after, with keyword DESC).

  • Two arrays are sorted by comparing their elements, in order. When two corresponding elements are unequal, the sort order of those elements determines the order of the two arrays. For example, with ascending sort order [4, 2, 5] sorts before [4, 2, 9] because 5 sorts before 9.

    If all elements of one array are equal to the corresponding elements of a longer array, the shorter array sorts before the longer one. For example, with ascending sort order [4, 2] sorts before [4, 2, 5], but it sorts after [4, 1, 5].

  • Two objects are sorted first by field name, then by field value, as follows:

    1. The members of each object are ordered by field name.

      Field names are compared as JSON string values, which uses the VARCHAR2 collation with binary ordering as represented in the AL32UTF8 character set.

    2. Members of the sorted objects (from step 1) are compared, in order:

      • When two corresponding field names are different, the object with the field name that sorts first is sorted before the other object (after, with keyword DESC).

      • When two corresponding field names are the same, the field values are compared, according to the JSON-type sort order. (That is, field values are compared recursively.) The order of the two objects being compared follows that of their field values.

  • Two scalars of different type families are sorted in this ascending order by family — so, for example, a number sorts before a string. (For descending sort the order is reversed.)

    1. null
    2. Numeric (number, double, float)
    3. string
    4. Nonidentifier binary (e.g., images)
    5. Identifier binary (e.g., values from extended objects with field $oid or $rawid)
    6. boolean
    7. Date and time points (date, timestamp, or timestamp with time zone)
    8. yearmonthInterval
    9. daysecondInterval

    There are two separate families for both date-time interval values and binary values, because the values need to be compared and sorted separately. Different months can have a different number of days. Binary values that are used as identifiers are typically tested for equality; equality testing, even when possible, is typically not useful for nonidentifier binary values.

  • Two scalars of the same type family are sorted by the sort order defined for that family. For example, with ascending sort order, 100 sorts before 200.0 regardless of the numeric types used, and "cat" sorts before "dog" regardless of the character set used.

    The scalar JSON comparison used is the collation for the corresponding SQL scalar type, except that a JSON string comparison uses the VARCHAR2 collation with binary ordering as represented in the AL32UTF8 character set. For boolean values, false sorts before true (after, with keyword DESC).

You can compare any values of JSON type for purposes of sorting, such as is done by ORDER BY. But comparison for other purposes, for example in a comparison filter condition, is more limited.

Apart from sorting, you can compare any values that are in the same type family in any way. Values from different type families are always unequal: comparison for equality (==) yields false and comparison for inequality (!=, <>) yields true). Comparisons <, <=, >=, and > are meaningful and useful only within the same family; if used with values from different families then the comparison condition returns false. For example, a JSON object, number, or boolean is neither greater than nor less than a JSON array, string, or date.

Tip:

Because json_scalar returns NULL by default for nonscalar input, and because comparison involving a nonscalar JSON value can be more costly than scalar-with-scalar comparison, a simple manual optimization when ordering or comparing JSON data is to do so after wrapping it with json_scalar, thus effectively pruning nonscalars from the data to be compared. (More precisely, they are replaced with NULL, which is quickly compared.)

For example instead of this:

SELECT data FROM customers c
  ORDER BY c.data.revenue;

Use this:

SELECT data FROM customers c
  ORDER BY json_scalar(c.data.revenue);

You can use item method type() to help you identify the type family of a JSON value, which makes it useful for purposes of comparison or indexing. However, it provides only a rough guide for this, because it generally reports only on the SQL data type from which the JSON value was derived, or to which the JSON value can be mapped.

  • null type family: type() returns "null".

  • Numeric type family: type() returns different type names for different kinds of numeric value:

    • "double", for a JSON value (a number) that corresponds to a SQL BINARY_DOUBLE value. This includes, for example, values that were derived from an extended object with $numberDouble.

    • "float", for a JSON value (a number) that corresponds to a SQL BINARY_FLOAT value. This includes, for example, values that were derived from an extended object with $numberFloat.

    • "number", for a JSON value (a number) that was derived from either (1) a textual JSON number or a string numeral (corresponding to the standard JSON-language number type) or (2) an extended object with $numberInt, $numberDecimal, or $numberLong.

  • string type family: type() returns "string".

  • Binary type families: type() returns "binary" for both the identifier and nonidentifier binary families, that is, a value that corresponds to a SQL RAW value. type() cannot distinguish values of these two families.

  • boolean type family: type() returns "boolean".

  • Date and time point family returns different type names for different kinds:

    • "date" for a value that corresponds to a SQL DATE value. This includes, for example, values that were derived from an extended object with $oracleDate.

    • "timestamp" for a value that corresponds to a SQL TIMESTAMP value. This includes, for example, values that were derived from an extended object with $oracleTimestamp.

    • "timestamp with time zone" for a value that corresponds to a SQL TIMESTAMP WITH TIME ZONE value. This includes, for example, values that were derived from an extended object with $date or $oracleTimestampTZ. (A $date field has a timestamp-with-timezone value, because it allows fractional seconds, and the value is given for Coordinated Universal Time (UTC).)

  • yearMonthInterval type family: type() returns "yearmonthInterval" for a value that corresponds to a SQL INTERVAL YEAR TO MONTH value. This includes, for example, values that were derived from an extended object with $intervalYearMonth.

  • daysecondInterval type family: type() returns "daysecondInterval" for a value that corresponds to a SQL INTERVAL DAY TO SECOND value. This includes, for example, values that were derived from an extended object with $intervalDaySecond.

  • vector type family: type() returns "vector".Foot 5

Related Topics

See Also:

Data Type Comparison Rules in Oracle Database SQL Language Reference for information about how Oracle Database compares values within each data type

Parent topic: JSON Data Type

2.2.7 Comparison of SQL Values With JSON Data Type Values

When comparing a JSON-type value with a SQL value of a type other than JSON, the same rules apply as when comparing two JSON-type values of the same family (e.g. numeric values), provided that the SQL type corresponds to one of the JSON-language types of that family.

(Comparison of two JSON-type values is described in Comparison and Sorting of JSON Data Type Values.)

This is the only useful kind of comparison to be made directly between JSON values and SQL values. All other such comparisons just return false or (exceptionally) raise an error.

For example, this is a useful comparison of a JSON-type numeric value with a SQL NUMBER value, assuming that column DATA is of JSON type: 

SELECT c.data FROM customers
  ORDER BY c.data.address.zip <= 12345;

That query is in fact equivalent to this one:

SELECT c.data FROM customers
  ORDER BY c.data.address.zip.numberOnly() = 12345;

Only numeric JSON values can make such a comparison true. If the value of field zip is not numeric, that is, it's not in the number family, then the comparison returns false. For example, if zip is the string "314" then the comparison is false — no type conversion is performed automatically to convert the string "314" to the number 314.

If the SQL value to be compared is not of a type that corresponds to any JSON-language scalar type then an error is raised.

For example, an SDO_GEOMETRY value can't be compared directly with any JSON value: 

ERROR at line 1:ORA-00932: expression is of data type
MDSYS.SDO_GEOMETRY, which is incompatible with expected data type JSON

If the JSON value to be compared is nonscalar (an object or array) then the comparison returns false, regardless of the SQL value.

If you are unsure of the JSON-language type of the JSON-type value, and you want to convert it (when possible) to a type that's compatible with the SQL value being compared, you can use a type-conversion item method (see Data-Type Conversion Item Methods).

For example, this query uses item method number() to interpret the value of field zip as would function json_value with clause RETURNING NUMBER, so a zip JSON string "314" is converted to the JSON number 314 (and the comparison is true). 

SELECT data FROM customers
  ORDER BY c.data.address.zip.number() = 12345

Tip:

You can use a JSON Data Guide to identify the data types of values inside unknown JSON data.

If the SQL value to be compared is a string that represents a JSON value, you can convert it to a JSON-type value using the JSON constructor. This allows you to compare any JSON values, whether scalar or nonscalar. (In effect, this is not a case of comparing JSON values with SQL values.)

For example, this query compares the value of JSON-type field zip with the JSON-type number 12345, which the constructor parses from the SQL string '12345': 

SELECT c.data FROM customers
  ORDER BY c.data.address.zip <= JSON('12345');

And this query compares the value of field address with the literal JSON object shown: 

SELECT c.data FROM customers
  ORDER BY c.data.address <=
    JSON('{"street"  : "200 Sporting Green",
           "city"    : "South San Francisco",
           "state"   : "CA",
           "zipCode" : 99236');

If the JSON value to be compared is textual (not JSON type) then it is treated as a SQL string (VARCHAR2) for the comparison.

For example, if column textualjson is of type VARCHAR2 and the value of field zip is the JSON number 314, then this comparison is false because 314 is lexicographically greater than 12345. 

SELECT c.textualjson FROM customers
  ORDER BY c.textualjson.address.zip <= '12345';

The JSON number is converted to the SQL string '314', and that is compared with the SQL string '12345' — a string comparison, not a number comparison.

Related Topics

Parent topic: JSON Data Type


Footnote Legend

Footnote 1: Keywords ASCII, PRETTY, ORDERED, TRUNCATE, and EXTENDED are Oracle extensions; they are not part of the SQL/JSON standard.
Footnote 5: Keep in mind that a scalar vector value is not handled as a scalar. Instead, for comparison purposes it is handled as an array of numbers.