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.

You can use PL/SQL procedure DBMS_CLOUD.copy_collection to create a JSON document collection from a file of textual JSON data such as that produced by common NoSQL databases, including Oracle NoSQL Database. The document collection uses Oracle's native binary JSON format, OSON.

When you use procedure copy_collection, if you specify ejson as the value of the type parameter, special objects in the textual input JSON data are interpreted as representing JSON scalar values, and they are replaced by those scalar values in the resulting OSON-format data. These JSON scalar values can be of types that are not part of the JSON standard. This is in fact the typical use of such extended objects: to represent nonstandard scalar JSON values in standard JSON textual data.

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.

If ejson is not used as the value of copy_collection parameter type, then no translation is made of such extended objects to JSON scalars — the objects are simply kept as is, but converted from textual to 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.

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.

Some scalar values are distinguishable internally, even when they are reported (by SQL/JSON path-language item method type()) as having 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 SQL/JSON path-language 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 3-1 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 3-1 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.