JSON Processing in Oracle SQL and PL/SQL

Storing JSON in Oracle

JSON documents can be stored in VARCHAR2 (up to 32767 bytes), CLOB, BLOB, or the native JSON type (Oracle 21c+):

CREATE TABLE api_requests (
  request_id  NUMBER PRIMARY KEY,
  payload     CLOB CHECK (payload IS JSON),
  created_dt  DATE DEFAULT SYSDATE
);

The IS JSON constraint validates JSON syntax on insert.

Querying JSON with dot notation

Oracle’s dot notation is the most readable way to extract JSON values:

SELECT
  jr.payload.orderId,
  jr.payload.customer.name,
  jr.payload.lines[0].productCode
FROM api_requests jr
WHERE jr.payload.status = 'PENDING';

JSON_VALUE: extracting a scalar

SELECT JSON_VALUE(payload, '$.customer.email') AS email
FROM api_requests
WHERE JSON_VALUE(payload, '$.status') = 'ACTIVE';

JSON_QUERY: extracting objects or arrays

SELECT JSON_QUERY(payload, '$.lines') AS order_lines
FROM api_requests WHERE request_id = 1;

JSON_TABLE: converting JSON to relational rows

The most powerful function — converts JSON arrays to table rows:

SELECT jt.*
FROM api_requests r,
JSON_TABLE(r.payload, '$.lines[*]'
  COLUMNS (
    line_num     NUMBER       PATH '$.lineNumber',
    product_code VARCHAR2(20) PATH '$.productCode',
    quantity     NUMBER       PATH '$.qty',
    unit_price   NUMBER       PATH '$.price'
  )
) jt
WHERE r.request_id = 1;

JSON_OBJECT and JSON_ARRAY: generating JSON

SELECT JSON_OBJECT(
  'employeeId' VALUE employee_id,
  'name' VALUE first_name || ' ' || last_name,
  'salary' VALUE salary,
  'department' VALUE JSON_OBJECT('id' VALUE department_id)
) AS employee_json
FROM employees WHERE department_id = 20;

JSON indexes for performance

For frequent JSON field queries, create function-based or JSON search indexes:

-- Function-based index for a frequently queried field:
CREATE INDEX idx_json_status ON api_requests (
  JSON_VALUE(payload, '$.status')
);

-- Full-text JSON search index:
CREATE SEARCH INDEX idx_json_search ON api_requests (payload) FOR JSON;