Oracle Cloud Infrastructure Documentation

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

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.

Table 3-1 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 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
$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

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.