This repository is a PL/SQL-focused Oracle JSON reference project. It walks through a progression of JSON features in Oracle Database, from basic JSON table design and querying to indexing, duality views, materialized views, partitioning, compression, and monitoring.
Examples for creating JSON tables, including:
- JSON collection tables
- Tables with a JSON data type
- Autonomous AI JSON and Oracle Database 26ai examples
SQL examples for querying JSON data, including:
JSON_VALUEJSON_EXISTSJSON_QUERYJSON_TABLE- Common table expression patterns for JSON queries
Examples showing how to improve JSON query performance with indexes, including:
- Unique indexes
- Composite indexes
- Search indexes
- Multivalue indexes
- Partial indexes
- Explain plans for indexed JSON queries
Demonstrates Oracle JSON relational duality views with a customer / products / sales relational data model.
Shows how to build relational views over JSON tables and how to work with JSON collection views using aggregations such as SUM, AVG, GROUP BY, and window functions.
Covers performance patterns with materialized views over JSON data, including:
- Materialized view usage
- Automatic query rewrite
- Aggregation support
Demonstrates JSON_DATAGUIDE use cases, including:
- Creating relational views from JSON dataguides
- Projecting scalar fields using path expressions
Examples for partitioning JSON collection tables.
Demonstrates JSON collection table compression options, including:
COMPRESSION MEDIUMCOMPRESSION HIGH
Shows how to monitor JSON queries using SQL Monitor reports and DBMS_SQLDIAG.
Demonstrates how to archive and manage IoT-style JSON data in Oracle Database, including:
- JSON-based IoT event storage
- Time-series style archival patterns
- JSON querying for device telemetry
- Archive table management techniques
- Efficient handling of high-volume JSON payloads
High-level project documentation. It explains the repository theme, the feature areas covered, and links the examples together as a learning path.
Contains the Universal Permissive License (UPL), Version 1.0.
- Oracle JSON tables
- JSON querying
- JSON indexing
- Relational duality views
- JSON collection views
- Materialized views on JSON data
- JSON dataguide
- Partitioning JSON data
- JSON compression
- Query monitoring
This repository reads like a practical Oracle JSON workshop: each SQL file focuses on one feature area and builds a clear story around how JSON can be stored, queried, optimized, monitored, and archived in Oracle Database, including modern IoT-style JSON workloads.