8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON Support Enhancements in Oracle Database 12c Release 2 (12.2)
Oracle 12c Release 2 (12.2) 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.
- JSON Developers Guide
- The JSON conditions and functions from 12.1 are now supported in PL/SQL
- SQL/JSON Functions
- PL/SQL Object Types for JSON
- Rewrites to JSON_TABLE
- Simplified Syntax for JSON Search Indexes
- JSON Data Guide
- {USER|ALL|DBA}_JSON_COLUMNS Views
Related articles.
- SQL/JSON Functions in Oracle Database 12c Release 2 (12.2)
- PL/SQL Object Types for JSON in Oracle Database 12c Release 2 (12.2)
- JSON Data Guide in Oracle Database 12c Release 2 (12.2)
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
JSON Developers Guide
The documentation for Oracle Database 12c Release 2 (12.2) now includes a new manual, which brings together all the support for JSON inside the database.
The JSON conditions and functions from 12.1 are now supported in PL/SQL
Oracle Database 12c Release 1(12.1) introduced a number of JSON conditions and functions available from SQL. Oracle Database 12c Release 2 (12.2) now supports these conditions and functions in PL/SQL also.
The following example shows the IS JSON
and JSON_EXISTS
conditions and the JSON_VALUE
and JSON_QUERY
functions used in PL/SQL.
SET SERVEROUTPUT ON DECLARE PROCEDURE test_json (p_json IN CLOB) IS BEGIN IF p_json IS JSON THEN DBMS_OUTPUT.put_line(p_json || ' - IS JSON'); IF JSON_EXISTS(p_json, '$.employee_no' FALSE ON ERROR) THEN DBMS_OUTPUT.put_line('employee_no = ' || JSON_VALUE(p_json, '$.employee_no')); DBMS_OUTPUT.put_line('employee_no = ' || JSON_QUERY(p_json, '$.employee_no' WITH CONDITIONAL WRAPPER)); END IF; ELSE DBMS_OUTPUT.put_line(p_json || ' - IS NOT JSON'); END IF; END; BEGIN test_json('banana'); test_json('{ "employee_no":9999 }'); END; / banana - IS NOT JSON { "employee_no":9999 } - IS JSON employee_no = 9999 employee_no = [9999] PL/SQL procedure successfully completed. SQL>
The following article explains these conditions and functions in more depth.
SQL/JSON Functions
Oracle Database 12c Release 2 (12.2) includes new SQL/JSON functions to generate JSON data directly from SQL. You can read more about this in the following article.
PL/SQL Object Types for JSON
Oracle Database 12c Release 2 (12.2) includes new JSON object types to support the in-memory parsing, generation and update of JSON data directly from PL/SQL. You can read more about this in the following article.
Rewrites to JSON_TABLE
In Oracle 12.2 multiple calls to JSON_EXISTS
, JSON_VALUE
, and JSON_QUERY
may be rewritten to fewer JSON_TABLE
calls to improve performance.
Simplified Syntax for JSON Search Indexes
A new simplified JSON search index syntax has been introduced in Oracle Database 12.2. There are no differences in the way the resulting JSON search index performs.
JSON Data Guide
Simplify the interaction with JSON data stored in the database using the JSON Data Guide functionality introduced in Oracle Database 12c Release 2 (12.2).
{USER|ALL|DBA}_JSON_COLUMNS Views
The {USER|ALL|DBA}_JSON_COLUMNS
views display the table columns containing JSON data, that is table columns with an IS JSON
check constraint.
In 18c these views also list view columns that contain JSON data.
For more information see:
- JSON Developer's Guide
- Using PL/SQL Object Types for JSON
- JSON Data Structures
- SQL/JSON Functions in Oracle Database 12c Release 2 (12.2)
- PL/SQL Object Types for JSON in Oracle Database 12c Release 2 (12.2)
- JSON Data Guide in Oracle Database 12c Release 2 (12.2)
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...