JSON in Oracle: JSON_TABLE, JSON_VALUE, and Dot Notation for PL/SQL Developers

JSON Is a First Class Citizen in Oracle

Since Oracle 12c Release 1, JSON has been natively supported in the database. Oracle provides multiple ways to query, create, and manipulate JSON data directly in SQL and PL/SQL. For developers building APEX applications that consume or produce JSON, whether from REST APIs, modern web frontends, or NoSQL-style document storage, these features eliminate the need for custom parsing code.

Dot Notation: The Simplest Approach

If your table has a JSON column, you can access fields using simple dot notation:

CREATE TABLE orders (
    order_id   NUMBER PRIMARY KEY,
    order_data VARCHAR2(4000)
        CONSTRAINT order_data_json CHECK (order_data IS JSON)
);

SELECT o.order_id,
       o.order_data.customer.name.string() AS customer_name,
       o.order_data.total.number() AS total
  FROM orders o
 WHERE o.order_data.status.string() = 'SHIPPED';

JSON_VALUE: Extracting Scalar Values

JSON_VALUE extracts a single scalar value from a JSON document using a JSON path expression:

SELECT order_id,
       JSON_VALUE(order_data, '$.customer.name') AS customer_name,
       JSON_VALUE(order_data, '$.total' RETURNING NUMBER) AS total,
       JSON_VALUE(order_data, '$.order_date' RETURNING DATE) AS order_date
  FROM orders
 WHERE JSON_VALUE(order_data, '$.status') = 'SHIPPED';

JSON_TABLE: Relational Views of JSON Arrays

The most powerful JSON function is JSON_TABLE, which converts a JSON array into relational rows:

SELECT o.order_id,
       jt.product_name,
       jt.quantity,
       jt.unit_price
  FROM orders o,
       JSON_TABLE(o.order_data, '$.items[*]'
           COLUMNS (
               product_name VARCHAR2(100) PATH '$.product',
               quantity     NUMBER        PATH '$.qty',
               unit_price   NUMBER        PATH '$.price'
           )
       ) jt
 WHERE o.order_id = 1001;

Generating JSON From Relational Data

Going the other direction, use JSON_OBJECT, JSON_ARRAY, and JSON_ARRAYAGG to construct JSON from relational queries:

SELECT JSON_OBJECT(
           'employee_id' VALUE employee_id,
           'name'        VALUE first_name || ' ' || last_name,
           'department'  VALUE department_name
       ) AS emp_json
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id;

These functions are particularly useful for building REST API responses in ORDS or for populating APEX page items with structured data for JavaScript consumption.