Using JSON in Oracle Database 21c (Advanced)

Two-day (16 hours) hands-on training: theory + demos + labs + performance tuning
Target: Oracle 21c
Level: Advanced
Format: PowerPoint + SQL script pack

Learning objectives

  • Model hybrid relational + JSON data with the native JSON type.
  • Query JSON efficiently using JSON_VALUE, JSON_QUERY, JSON_EXISTS.
  • Flatten documents using JSON_TABLE (nested paths, ordinality, joins).
  • Perform partial updates using JSON_TRANSFORM and JSON_MERGEPATCH.
  • Design indexing strategies: search index vs targeted indexes via generated columns.
  • Validate structure (constraints + schema validation patterns).
  • Generate JSON APIs from SQL/PLSQL using JSON_OBJECT, JSON_ARRAYAGG.
  • Tune workloads with execution plans and path standardization.

2-day agenda (16 hours)

Day 1 – Foundations & Core Features

  • JSON in Oracle 21c: native JSON type, storage & constraints
  • Sample schema setup (customers, products, order documents)
  • Querying JSON: JSON_VALUE / JSON_QUERY / JSON_EXISTS
  • Arrays and nested structures; set-based access
  • Lab blocks and discussion

Day 2 – Advanced JSON & Performance

  • JSON_TABLE advanced projections + joins
  • Partial updates: JSON_TRANSFORM + JSON_MERGEPATCH
  • Indexing strategy: generated columns + targeted indexes + search index
  • Validation patterns + PL/SQL / SQL JSON generation
  • Capstone: hybrid report + JSON API payload

Quickstart lab snippets (copy/paste)

These match the provided script pack. Run 00_setup_schema.sql as a privileged user, then connect as SOFT_JSON.

Create a JSON table and insert a document (Oracle 21c JSON type)
CREATE TABLE order_docs (
  order_id     NUMBER PRIMARY KEY,
  customer_id  NUMBER NOT NULL,
  created_at   DATE DEFAULT SYSDATE NOT NULL,
  doc          JSON NOT NULL
);

INSERT INTO order_docs(order_id, customer_id, doc)
VALUES (50000, 1000, JSON('{"orderNumber":"ORD-50000","items":[{"sku":"SKU-1","qty":1,"unitPrice":10.0}]}'));
Query + flatten with JSON_TABLE
SELECT o.order_id, jt.sku, jt.qty, jt.unit_price
FROM order_docs o
CROSS JOIN JSON_TABLE(
  o.doc,
  '$.items[*]'
  COLUMNS (
    sku        VARCHAR2(40) PATH '$.sku',
    qty        NUMBER       PATH '$.qty',
    unit_price NUMBER       PATH '$.unitPrice'
  )
) jt;
Partial update with JSON_TRANSFORM
UPDATE order_docs o
SET o.doc = JSON_TRANSFORM(
  o.doc,
  SET '$.attributes.priority' = true,
  SET '$.attributes.updatedBy' = 'training'
)
WHERE JSON_EXISTS(o.doc, '$?(@.channel == "MOBILE")');