- Elevator pitch -

“The happy Data couple cordially invites you to the Wedding of Mr. JSON and Ms. Relational.”

Mr. JSON began courtship in Oracle version 12.1.0.2 to the delight of Ms. Relational. In every Oracle version since then, the two have grown closer, right up to the JSON Relational Duality marriage in 23c.

- Description -

Native support of JSON within Oracle Database became available in version 12.1.0.2 and has greatly expanded and improved in every version since. This session will give you an overview and examples of the various functionalities to marry JSON and Relational data across the versions from 12.1.0.2 until 23c including:

• Different datatypes to store JSON
• Discover content of schemaless JSON with Dataguide
• Validate JSON with a JSON Schema
• Querying JSON data in different ways
• Creating JSON data in different ways
• Modifying JSON data
• Using JSON Relational Duality view to work with same data both as JSON and as relational
• JSON in PL/SQL
• GeoJSON and Spatial

- Notes -

JSON functionality in the database has grown a lot since the simple functions introduced in version 12.1.0.2. This session covers the JSON functionality from 12.1.0.2 to 23c, including:

• Query JSON: JSON_VALUE, JSON_QUERY, JSON_TABLE, JSON_EXISTS, JSON_EQUAL
• dot-notation and simplified JSON_TABLE
• JSON path expressions, including item methods
• Predicates in JSON path expressions
• JSON search index improving search performance using variant of Oracle Text
• Dataguide detecting metadata of the JSON content
• Dataguide autocreation of JSON schema
• Dataguide autocreation of relational views on the JSON content
• Dataguide autocreation of virtual columns per key in key:value pairs
• JSON creation: JSON_ARRAY, JSON_OBJECT, JSON_ARRAYAGG, JSON_OBJECTAGG
• Updating JSON: JSON_MERGEPATCH and JSON_TRANSFORM
• PL/SQL API: JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T
• GeoJSON to SDO_GEOMETRY and GeoJSON in Dataguide
• JSON_SERIALIZE
• JSON datatype and JSON Schema
• JSON Relational Duality View

Presented at:
- POUG 2023
- DOUG 2023
- ODTUG Kscope 2024
- Oracle CloudWorld 2024