json-support-in-oracle-database-18c

JSON Support Enhancements in Oracle Database 18c

Oracle 18c includes several enhancements to the JSON functionality in
the database. This article details some of them, as well as linking to
separate articles on others.
* [46]LOB Return Types for JSON_VALUE, JSON_QUERY, and JSON_TABLE
Functions
* [47]LOB Support for SQL/JSON Generation Functions
* [48]TREAT(... AS JSON)
* [49]JSON_EQUAL Condition
* [50]JSON Data Guide Enhancements
* [51]JSON_TABLE Enhancements
* [52]TO_UTC_TIMESTAMP_TZ
* [53]SODA for PL/SQL
* [54]Miscellaneous

Related articles.
* [55]TREAT(... AS JSON) in Oracle Database 18c
* [56]JSON_EQUAL Condition in Oracle Database 18c
* [57]JSON Data Guide Enhancements in Oracle Database 18c
* [58]JSON_TABLE Enhancements in Oracle Database 18c
* [59]SODA for PL/SQL in Oracle Database 18c
* JSON Quick Links : [60]Introduction, [61]12cR1, [62]12cR2, [63]18c,
[64]All Articles

LOB Return Types for JSON_VALUE, JSON_QUERY, and JSON_TABLE Functions

The JSON_VALUE function now supports the following return types,
including the new CLOB and BLOB return types.
{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| NUMBER [ ( precision [, scale] ) ]
| DATE
| TIMESTAMP
| TIMESTAMP WITH TIME ZONE
| CLOB
| BLOB
| SDO_GEOMETRY
}

The JSON_QUERY function now supports the following return types,
including the new CLOB and BLOB return types.
{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| CLOB
| BLOB
}

The return values of the JSON_TABLE function depends on the column
definitions used. If a column is defined using the FORMAT JSON clause
the JSON_QUERY return types are supported, otherwise the JSON_VALUE
return types are supported.

You can see examples of using these functions [65]here.

LOB Support for SQL/JSON Generation Functions

The SQL/JSON generation functions JSON_OBJECT, JSON_OBJECTAGG,
JSON_ARRAY and JSON_ARRAY can now return LOB results, with the output
determined by the RETURNING clause. The valid options are as follows.
{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| CLOB
| BLOB
}

As with 12.2, if no RETURNING clause is specified or if RETURNING
VARCHAR2 is specified is specified, the result is returned as a
VARCHAR2(4000) type.

You can see examples of using these functions [66]here.

Not surprisingly, the SQL/JSON generation functions have also been
extended to accept CLOB and BLOB input parameters, as well as some
additional types. The following data types are now supported as input
expressions.
-- 12cR2
NUMBER
VARCHAR2
DATE
TIMESTAMP

-- 18c
BINARY_DOUBLE
BINARY_FLOAT
CLOB
BLOB
NVARCHAR2
RAW
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

The way these types are handled is described fully [67]here, but in
summary.
* Strings Types : Special characters are escaped and the string is
enclosed in double quotes.
* Numeric Types : Not enclosed by quotes.
* RAW and BLOB Types: Hexadecimal JSON string, enclosed in double
quotes.
* DATE, TIMESTAMP and INTERVAL Types : Converted to ISO 8601 format
and enclosed in double quotes.
* BOOLEAN Types : JSON true or false, not enclosed by quotes.
* NULL : JSON null, not enclosed by quotes.
* EMPTY_CLOB and EMPTY_BLOB : JSON empty string, empty double quotes.

TREAT(... AS JSON)

In Oracle 18c the TREAT function can be used in SQL to change the
declared type of an expression to JSON, where the expression is an
object containing JSON data. You can read more about this in the
following article.
* [68]TREAT(... AS JSON) in Oracle Database 18c

JSON_EQUAL Condition

The JSON_EQUAL condition was introduced in 18c to allow JSON documents
to be compared regardless of member order of document formatting. You
can read more about this in the following article.
* [69]JSON_EQUAL Condition in Oracle Database 18c

JSON Data Guide Enhancements

Oracle 18c includes a number of enhancements to the JSON data guide
functionality, which you can read more about this in the following
article.
* [70]JSON Data Guide Enhancements in Oracle Database 18c

JSON_TABLE Enhancements

Oracle 18c includes simplified syntax for the JSON_TABLE function, and
support for ON STATEMENT materialized views. You can read more about
these enhancements in the following article.
* [71]JSON_TABLE Enhancements in Oracle Database 18c

TO_UTC_TIMESTAMP_TZ

Oracle 18c introduced the new TO_UTC_TIMESTAMP_TZ function to help deal
with ISO 8601 date strings. You can read more about this in the
following article.
* [72]TO_UTC_TIMESTAMP_TZ Function in Oracle Database 18c

SODA for PL/SQL

Oracle 18c introduced a PL/SQL API for interacting directly with SODA
collections and documents. You can read more about this in the
following article.
* [73]SODA for PL/SQL in Oracle Database 18c

Miscellaneous

* The {USER|ALL|DBA}_JSON_COLUMNS views now list views as well as
tables that contain JSON columns.
* There are a number of enhancements related to JSON data and
sharding, as described [74]here.
* The documentation claims the performance of LOB storage for JSON
data has improved when reading and writing an entire JSON document
that is stored as a LOB.
* JSON Search Indexes : The maximum JSON field name length has been
extended from 64 bytes to 255 bytes. Upgraded databases will need
their JSON search indexes rebuilt to take advantage of this.
* The SQL/JSON generation functions can include an optional keyword
STRICT, which checks inputs for well formed JSON. If the JSON is
not well formed an error will be raised.
* New SQL/JSON path expression item methods have been added, with the
full list described [75]here. All item methods are supported in
path expressions for the JSON_VALUE, JSON_QUERY and JSON_TABLE
SQL/JSON functions. Where appropriate these item methods support
LOBs.

For more information see:
* [76]TREAT(... AS JSON) in Oracle Database 18c
* [77]JSON_EQUAL Condition in Oracle Database 18c
* [78]JSON Data Guide Enhancements in Oracle Database 18c
* [79]JSON_TABLE Enhancements in Oracle Database 18c
* [80]SODA for PL/SQL in Oracle Database 18c
* JSON Quick Links : [81]Introduction, [82]12cR1, [83]12cR2, [84]18c,
[85]All Articles

Hope this helps. Regards Tim...

[86]Back to the Top.
[INS: :INS]


[87]0 comments, read/add them...

[88]Home | [89]Articles | [90]Scripts | [91]Blog | [92]Certification |
[93]Misc | [94]About

[95]About Tim Hall
[96]Copyright & Disclaimer

Références

Liens visibles
1. https://oracle-base.com/articles/18c/json-support-in-oracle-database-18c
2. https://oracle-base.com/articles/8i/articles-8i
3. https://oracle-base.com/articles/9i/articles-9i
4. https://oracle-base.com/articles/10g/articles-10g
5. https://oracle-base.com/articles/11g/articles-11g
6. https://oracle-base.com/articles/12c/articles-12c
7. https://oracle-base.com/articles/13c/articles-13c
8. https://oracle-base.com/articles/18c/articles-18c
9. https://oracle-base.com/articles/misc/articles-misc
10. https://oracle-base.com/articles/plsql/articles-plsql
--------------------------
Source .... : https://oracle-base.com/articles/18c/json-support-in-oracle-database-18c
--------------------------