How to Parse JSON in Oracle

Why parse JSON in Oracle?

Applications often store semi-structured attributes—shopping cart details, payment metadata, or address formats—as JSON. Oracle’s JSON functions let you filter, extract, and join this data without unloading it into separate columns, keeping queries simple and performant.

What is the basic syntax of JSON_VALUE?

JSON_VALUE returns a scalar (number, string, date, Boolean) from a JSON column. Use it when you only need one value, such as pulling the city from a shipping address stored in Orders.extra_info.

Example: get city from order

SELECT id,
JSON_VALUE(extra_info, '$.shipping.city') AS city
FROM Orders
WHERE order_date >= DATE '2024-01-01';

How do I extract an object or array with JSON_QUERY?

JSON_QUERY returns JSON fragments (objects or arrays). Wrap the result in FORMAT JSON when you need to pass it to APIs or client code.

Example: return full shipping address

SELECT id,
JSON_QUERY(extra_info, '$.shipping' RETURNING CLOB FORMAT JSON) AS shipping_json
FROM Orders
WHERE total_amount > 100;

How do I flatten multiple keys with JSON_TABLE?

JSON_TABLE turns JSON arrays or objects into a relational table you can join or filter. It’s perfect for line-item analysis in ecommerce.

Example: explode OrderItems JSON column

SELECT o.id AS order_id,
jt.product_id,
jt.quantity
FROM Orders o,
JSON_TABLE(o.extra_info, '$.items[*]' COLUMNS (
product_id NUMBER PATH '$.productId',
quantity NUMBER PATH '$.qty'
)) jt;

How can I join parsed JSON with Products?

Combine JSON_TABLE output with regular tables to enrich reports, validate prices, or detect out-of-stock items.

Example: flag items with low stock

SELECT c.name AS customer,
p.name AS product,
jt.quantity,
p.stock
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
CROSS JOIN JSON_TABLE(o.extra_info, '$.items[*]' COLUMNS (
product_id NUMBER PATH '$.productId',
quantity NUMBER PATH '$.qty'
)) jt
JOIN Products p ON p.id = jt.product_id
WHERE p.stock < jt.quantity;

What are best practices for JSON parsing?

1) Add a check constraint IS JSON to ensure valid data. 2) Create functional indexes on frequently queried paths using JSON_VALUE. 3) Cast results to proper data types for accurate comparisons. 4) Use KEEP or ERROR ON ERROR options to control bad data behavior.