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 typeJSON
, (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 typeJSON
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 createJSON
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.
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).
- Data Types for JSON Data
SQL data typeJSON
is Oracle's binary JSON format for fast query and update. It extends the standard JSON scalar types (number, string, Boolean, andnull
), 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 usingJSON
data type constructorJSON
, 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 useJSON
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, aBINARY_FLOAT
SQL value results in a float JSON value. -
Use Oracle SQL function
json_scalar
. For example, applying it to aBINARY_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 aRETURNING
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
, andjson_table
.These evaluate SQL/JSON path expressions against JSON data to produce SQL values.
-
Oracle SQL condition
json_textcontains
and SQL/JSON conditionsjson_exists
,is json
, andis not json
.Condition
json_exists
checks for the existence of given JSON data;json_textcontains
provides full-text querying of JSON data; andis json
andis not json
check whether given JSON data is well-formed.json_exists
andjson_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
andjson_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
, andjson_arrayagg
.These gather SQL data to produce JSON object and array data (as a SQL value).
-
Oracle SQL functions
json_serialize
andjson_scalar
, and Oracle SQL conditionjson_equal
.Function
json_serialize
returns a textual representation of JSON data;json_scalar
returns aJSON
type scalar value that corresponds to a given SQL scalar value; andjson_equal
tests whether two JSON values are the same. -
JSON
data type constructorJSON
.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
, andjson_equal
are Boolean functions. -
json_value
can return aBOOLEAN
value. -
json_scalar
can accept aBOOLEAN
value as argument, in which case it returns a BooleanJSON
type instance (true
orfalse
).
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
TheJSON
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 ofJSON
type. - Oracle SQL Function JSON_SCALAR
Oracle SQL functionjson_scalar
accepts a SQL scalar value as input and returns a corresponding JSON scalar value as aJSON
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 functionjson_serialize
takes JSON data (of any SQL data type,JSON
,VARCHAR2
,CLOB
, orBLOB
) as input and returns a textual representation of it (asVARCHAR2
,CLOB
, orBLOB
data).VARCHAR2(4000)
is the default return type. - JSON Constructor, JSON_SCALAR, and JSON_SERIALIZE: Summary
Relations among constructorJSON
, functionjson_scalar
, and functionjson_serialize
are summarized. - Migration of Textual JSON Data to JSON Type Data
Oracle recommends that you store JSON data in the database usingJSON
data type. You can migrate existing data from textual JSON storage (VARCHAR2
,CLOB
, orBLOB
) toJSON
type storage using Oracle GoldenGate or online redefinition.
See Also:
-
Oracle Database SQL Language Reference for information about
JSON
data type -
Oracle Database SQL Language Reference for information about constructor
JSON
-
Oracle Database SQL Language Reference for information about Oracle SQL function
json_scalar
-
Oracle Database SQL Language Reference for information about Oracle SQL function
json_serialize
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
.
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. It supports an error clause and a returning clause. You can specify
pretty-printing for the result, and you can truncate the result to fit the return
type.
Function json_serialize
always produces JSON data that
conforms to the JSON standard (RFC 8259). 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, however, also have values of the following scalar types, which Oracle has added to the JSON language: binary, date, timestamp, year-month interval, day-second interval, double, and float. JSON data of such types is converted when serialized according to Table 2-2.
Table 2-2 JSON_SERIALIZE Converts Oracle JSON-Language Types To Standard JSON-Language Types
Oracle Type | Standard Type | Notes |
---|---|---|
binary | string |
Conversion is equivalent to the use of SQL function
|
date | string |
The string is in an ISO 8601 date format:
YYYY-MM-DD. For example:
|
day-second interval | string |
The string is in an ISO 8601 duration format that
corresponds to a ds_iso_format specified for SQL function
PdDThHmMsS,
where d, h, m, and s are digit sequences for the
number of days, hours, minutes, and seconds, respectively. For example:
s can also be an integer-part digit sequence
followed by a decimal point and a fractional-part digit sequence. For example:
Any sequence whose value would be zero is omitted,
along with its designator. For example: |
double | number |
Conversion is equivalent to the use of SQL function
|
float | number |
Conversion is equivalent to the use of SQL function
|
timestamp | string |
The string is in an ISO 8601 date-with-time format:
YYY-MM-DDThh:mm:ss.ssssss. For example:
|
year-month interval | string |
The string is in an ISO 8601 duration format that
corresponds to a ym_iso_format specified for SQL function
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: If the number of years or months is
zero then it and its designator are omitted. Examples: |
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:
The default error handling for json_serialize
is NULL
ON ERROR
, which means that when an error occurs a SQL
NULL
value is returned — an error is not raised.
In particular, be aware of this in the context of returning serialized data that is
too large. If you are unsure of the return size, use RETURNING CLOB
or RETURNING BLOB
, to allow for more than the default of 4000 bytes
of VARCHAR2
data. Use clause ERROR ON ERROR
if you want to be sure to raise an error when the result is too large.
See Also:
-
Oracle Database SQL Language Reference for information about Oracle SQL function
json_serialize
-
Oracle Database SQL Language Reference for information about SQL function
rawtohex
- 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. See Example 10-1 for how to create a table with a BLOB
column of
JSON
data.
SELECT json_serialize(po_document PRETTY) FROM j_purchaseorder;
2.3.4 JSON Constructor, JSON_SCALAR, and JSON_SERIALIZE: Summary
Relations among constructor JSON
, function
json_scalar
, and function json_serialize
are
summarized.
Both constructor JSON
and Oracle SQL 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 JSON value returned by the constructor can be any that is supported by
Oracle: a JSON object, array, string, Boolean, null
, number, double,
float, binary, date, timestamp, day-second interval, or year-month interval value.
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, the result is a textual
(VARCHAR2
, CLOB
, or BLOB
)
representation of the JSON value, and any non-standard Oracle scalar JSON value is
returned as a standard JSON scalar value. 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-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 the 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.
Table 2-3 Effect of Constructor JSON and Oracle SQL Function JSON_SCALAR
Input SQL Value | SQL Type | JSON Value from JSON Constructor | JSON Scalar Value from JSON_SCALAR |
---|---|---|---|
{a:1} |
VARCHAR2 |
|
|
[1,2,3] |
VARCHAR2 |
|
|
true |
VARCHAR2 |
|
|
null |
VARCHAR2 |
|
|
SQL NULL |
VARCHAR2 |
|
|
"city" |
VARCHAR2 |
|
|
city |
VARCHAR2 |
Error — input is not valid JSON data
(there is no JSON scalar value |
|
3.14 |
VARCHAR2 |
|
|
3.14 |
NUMBER |
Error — not textual JSON data (SQL types
other than |
|
3.14 |
DOUBLE |
Error — not textual JSON data (SQL types
other than |
|
SQL date value from evaluating
to_date('20.07.1974') |
DATE |
Error — not textual JSON data |
|
SQL timestamp value from evaluating
to_timestamp_tz('2019-05-23 11:31:04.123 -8', 'YYYY-MM-DD
HH:MI:SS.FF TZH') |
TIMESTAMP WITH TIMEZONE |
Error — not textual JSON data |
|
See Also:
-
Oracle Database SQL Language Reference for information about constructor
JSON
-
Oracle Database SQL Language Reference for information about Oracle SQL function
json_scalar
-
Oracle Database SQL Language Reference for information about Oracle SQL function
json_serialize
Parent topic: JSON Data Type, To and From
2.3.5 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:
- https://www.oracle.com/middleware/technologies/goldengate.html for information about Oracle GoldenGate
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:
-
ISO/IEC 9075-2:2016, Information technology—Database languages—SQL—Part 2: Foundation (SQL/Foundation)
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