PLSQL

Oracle JSON_TABLE Example: Nested Arrays, Errors and Views

13 min read by
Oracle JSON_TABLE Example: Nested Arrays, Errors and Views
In this article
  1. How JSON_TABLE works
  2. Sample setup
  3. Example 1: flatten simple fields into columns
  4. Example 2: turn a JSON array into rows with NESTED PATH
  5. Example 3: handle optional fields
  6. Example 4: aggregate values from the array
  7. Example 5: catch bad data that becomes NULL silently
  8. Example 6: return a whole array with FORMAT JSON
  9. Example 7: hide the JSON behind a view
  10. Common mistakes
  11. Performance tips
  12. Frequently asked questions
  13. Summary

JSON_TABLE turns JSON stored in a column into ordinary rows and columns, so you can query it with normal SQL. You put it in the FROM clause, give it a row path such as '

Home
SQL
PLSQL
Interview Q&A
, and list the columns you want, each with its own path like '$.customer.name'. Arrays inside the document become extra rows through NESTED PATH.

This tutorial is a complete oracle json_table example on a small orders table. You will flatten simple fields, turn an array of line items into rows, number those rows, handle missing fields, catch bad data that Oracle hides by default, and wrap it all in a view. The examples follow the Oracle 19c documentation and also work in 21c and 23ai.

How JSON_TABLE works

Oracle describes JSON_TABLE as a row source. For every JSON value matched by the row path expression, it produces one row of a virtual table. The COLUMNS clause then reads values out of that matched JSON using column path expressions.

When you write FROM orders o, JSON_TABLE(o.order_doc, ...), the JSON_TABLE call is joined laterally to the orders table. That means it runs once per order row and can see that row's document. Each order can produce zero, one or many output rows.

Here are the parts you will use most:

PartWhat it does
Row path, e.g. ' Picks what one output row is. ' means the whole document, so one row per document.
PATH '$.field'Reads a single scalar value into a typed column, with JSON_VALUE rules.
NESTED PATH '$.arr[*]'Turns each array element into its own row, outer joined to the parent.
FOR ORDINALITYGenerates row numbers (type NUMBER) starting at 1.
EXISTS PATHReturns 'true' or 'false' depending on whether the path exists.
FORMAT JSONReturns a JSON fragment (object or array) as text instead of a scalar.

Path expressions use JavaScript style. Array indexes start at 0, and [*] means every element. Field names are matched case-sensitively, which causes one of the most common mistakes covered later.

Sample setup

Create an orders table that stores each order as a JSON document in a CLOB column. The IS JSON check constraint makes Oracle reject text that is not well-formed JSON. It is optional for JSON_TABLE, but it is good practice.

CREATE TABLE orders (
  order_id   NUMBER PRIMARY KEY,
  order_doc  CLOB CONSTRAINT orders_doc_is_json CHECK (order_doc IS JSON)
);

INSERT INTO orders VALUES (1,
  '{"customer":{"name":"Asha Rao","city":"Pune"},
    "status":"SHIPPED",
    "items":[{"sku":"KB-101","qty":2,"price":1500},
             {"sku":"MS-220","qty":1,"price":650}]}');

INSERT INTO orders VALUES (2,
  '{"customer":{"name":"Vikram Shah","city":"Mumbai"},
    "status":"PENDING",
    "coupon":"DIWALI10",
    "items":[{"sku":"MN-400","qty":1,"price":12500}]}');

INSERT INTO orders VALUES (3,
  '{"customer":{"name":"Neha Iyer","city":"Chennai"},
    "status":"CANCELLED",
    "items":[]}');

COMMIT;
Sample orders table with JSON documents

Order 1 has two line items, order 2 has one item and a coupon, and order 3 has an empty items array. These three shapes are enough to show every behaviour that matters.

Example 1: flatten simple fields into columns

Start with one row per order. The row path is '

Home
SQL
PLSQL
Interview Q&A
, the whole document, and each column reads one field.

SELECT o.order_id, jt.customer_name, jt.city, jt.status
  FROM orders o,
       JSON_TABLE(o.order_doc, '{{MAIN}}#39;
         COLUMNS (
           customer_name VARCHAR2(50) PATH '$.customer.name',
           city          VARCHAR2(30) PATH '$.customer.city',
           status        VARCHAR2(20) PATH '$.status'
         )) jt
 ORDER BY o.order_id;
One row per order
ORDER_ID CUSTOMER_NAME  CITY     STATUS
-------- -------------- -------- ---------
       1 Asha Rao       Pune     SHIPPED
       2 Vikram Shah    Mumbai   PENDING
       3 Neha Iyer      Chennai  CANCELLED
Output (expected output)

Each document matched '

Home
SQL
PLSQL
Interview Q&A
once, so each order produced one row. The path '$.customer.name' walks into the nested customer object. You choose the SQL type of every column, so jt.city is a real VARCHAR2 you can filter, sort and join on.

Example 2: turn a JSON array into rows with NESTED PATH

Line items live in an array. To get one row per item, add a NESTED PATH with its own COLUMNS clause. Parent columns repeat on every item row.

SELECT o.order_id, jt.customer_name, jt.line_no,
       jt.sku, jt.qty, jt.price
  FROM orders o,
       JSON_TABLE(o.order_doc, '{{MAIN}}#39;
         COLUMNS (
           customer_name VARCHAR2(50) PATH '$.customer.name',
           NESTED PATH '$.items[*]'
             COLUMNS (
               line_no FOR ORDINALITY,
               sku     VARCHAR2(20) PATH '$.sku',
               qty     NUMBER       PATH '$.qty',
               price   NUMBER       PATH '$.price'
             )
         )) jt
 ORDER BY o.order_id, jt.line_no;
One row per line item
ORDER_ID CUSTOMER_NAME  LINE_NO SKU     QTY  PRICE
-------- -------------- ------- ------- ---- ------
       1 Asha Rao             1 KB-101     2   1500
       1 Asha Rao             2 MS-220     1    650
       2 Vikram Shah          1 MN-400     1  12500
       3 Neha Iyer
Output (expected output)

Three things to notice in this result:

Why not use '$.items[*]' as the row path?

You can make the array itself the row path. It is shorter, but it changes the result:

SELECT o.order_id, jt.sku, jt.qty
  FROM orders o,
       JSON_TABLE(o.order_doc, '$.items[*]'
         COLUMNS (
           sku VARCHAR2(20) PATH '$.sku',
           qty NUMBER       PATH '$.qty'
         )) jt
 ORDER BY o.order_id;
Array as the row path
ORDER_ID SKU     QTY
-------- ------- ----
       1 KB-101     2
       1 MS-220     1
       2 MN-400     1
Output (expected output)

Order 3 has disappeared. When the row path matches nothing, JSON_TABLE returns no rows for that document, and the comma join drops the order. Also, inside the COLUMNS clause the context is now one item, so you cannot reach $.customer.name from there. Use '

Home
SQL
PLSQL
Interview Q&A
plus NESTED PATH when you need parent fields or want to keep orders with no items.

Example 3: handle optional fields

Real documents are not uniform. Only order 2 has a coupon. A missing field returns NULL by default, but you can supply a default with DEFAULT ... ON EMPTY, and test for presence with EXISTS PATH.

SELECT o.order_id, jt.coupon, jt.has_coupon
  FROM orders o,
       JSON_TABLE(o.order_doc, '{{MAIN}}#39;
         COLUMNS (
           coupon     VARCHAR2(20) PATH '$.coupon'
                      DEFAULT 'NONE' ON EMPTY,
           has_coupon VARCHAR2(5)  EXISTS PATH '$.coupon'
         )) jt
 ORDER BY o.order_id;
Default values and existence checks
ORDER_ID COUPON    HAS_COUPON
-------- --------- ----------
       1 NONE      false
       2 DIWALI10  true
       3 NONE      false
Output (expected output)

ON EMPTY applies only when the path finds nothing. If the field exists but holds a value that cannot be converted, that is an error, not an empty result, and ON ERROR decides what happens. The next example shows why that difference matters.

Example 4: aggregate values from the array

Once JSON is rows, all of SQL is available. Here is the order total and the number of line items, grouped by order:

SELECT o.order_id, jt.status,
       COUNT(jt.sku)                    AS item_lines,
       NVL(SUM(jt.qty * jt.price), 0)   AS order_total
  FROM orders o,
       JSON_TABLE(o.order_doc, '{{MAIN}}#39;
         COLUMNS (
           status VARCHAR2(20) PATH '$.status',
           NESTED PATH '$.items[*]'
             COLUMNS (
               sku   VARCHAR2(20) PATH '$.sku',
               qty   NUMBER       PATH '$.qty',
               price NUMBER       PATH '$.price'
             )
         )) jt
 GROUP BY o.order_id, jt.status
 ORDER BY o.order_id;
Order totals computed from the items array
ORDER_ID STATUS     ITEM_LINES ORDER_TOTAL
-------- ---------- ---------- -----------
       1 SHIPPED             2        3650
       2 PENDING             1       12500
       3 CANCELLED           0           0
Output (expected output)

Order 1 is 2 x 1500 + 1 x 650 = 3650. Order 3 keeps its row because of the nested outer join, and COUNT(jt.sku) correctly gives 0 since COUNT of a column ignores NULLs. COUNT(*) would have returned 1 for order 3, which is wrong.

Example 5: catch bad data that becomes NULL silently

The default error handler for JSON_TABLE, at both row and column level, is NULL ON ERROR. A value that cannot be converted to the column type does not raise an error. It becomes NULL. Add an order where the quantity was stored as text:

INSERT INTO orders VALUES (4,
  '{"customer":{"name":"Rahul Verma","city":"Delhi"},
    "status":"PENDING",
    "items":[{"sku":"KB-101","qty":"two","price":1500}]}');
COMMIT;
A valid JSON document with a bad quantity

The check constraint accepts it, because the document is well-formed JSON. Run Example 4 again and order 4 shows an order total of 0 with one item line, because qty became NULL and so did qty * price. Nothing tells you the data was wrong.

Option A: fail loudly with ERROR ON ERROR

SELECT o.order_id, jt.qty
  FROM orders o,
       JSON_TABLE(o.order_doc, '{{MAIN}}#39;
         COLUMNS (
           NESTED PATH '$.items[*]'
             COLUMNS (
               qty NUMBER PATH '$.qty' ERROR ON ERROR
             )
         )) jt;
Raise an error instead of returning NULL

Now the query stops with an error when it reaches order 4 instead of returning a wrong answer. You can also put ERROR ON ERROR right before COLUMNS to make it the default for every column. This is the safer setting for loading jobs and for testing a new query.

Option B: find the bad rows

To list the offending documents without failing, read the same field twice: once as NUMBER and once as text.

SELECT o.order_id, jt.sku, jt.qty_raw
  FROM orders o,
       JSON_TABLE(o.order_doc, '{{MAIN}}#39;
         COLUMNS (
           NESTED PATH '$.items[*]'
             COLUMNS (
               sku     VARCHAR2(20) PATH '$.sku',
               qty     NUMBER       PATH '$.qty',
               qty_raw VARCHAR2(20) PATH '$.qty'
             )
         )) jt
 WHERE jt.qty IS NULL
   AND jt.qty_raw IS NOT NULL;
Rows where qty exists but is not numeric
ORDER_ID SKU     QTY_RAW
-------- ------- -------
       4 KB-101  two
Output (expected output)

Example 6: return a whole array with FORMAT JSON

Sometimes you want the raw array as one value, for example to pass it to an API. A normal PATH column must target a scalar. Add FORMAT JSON to get the JSON text instead:

SELECT o.order_id, jt.items_json
  FROM orders o,
       JSON_TABLE(o.order_doc, '{{MAIN}}#39;
         COLUMNS (
           items_json VARCHAR2(400) FORMAT JSON PATH '$.items'
         )) jt
 WHERE o.order_id = 2;
Project the items array as JSON text
ORDER_ID ITEMS_JSON
-------- -------------------------------------------
       2 [{"sku":"MN-400","qty":1,"price":12500}]
Output (expected output)

Oracle may return the JSON text without the original spaces and line breaks, as shown here. Without FORMAT JSON, this column would be NULL, because an array is not a scalar.

Example 7: hide the JSON behind a view

The Oracle documentation calls a view over JSON_TABLE a common pattern. Report writers and other developers then query plain columns and never see a path expression.

CREATE OR REPLACE VIEW order_items_v AS
SELECT o.order_id, jt.*
  FROM orders o,
       JSON_TABLE(o.order_doc, '{{MAIN}}#39;
         COLUMNS (
           customer_name VARCHAR2(50) PATH '$.customer.name',
           city          VARCHAR2(30) PATH '$.customer.city',
           status        VARCHAR2(20) PATH '$.status',
           NESTED PATH '$.items[*]'
             COLUMNS (
               line_no FOR ORDINALITY,
               sku     VARCHAR2(20) PATH '$.sku',
               qty     NUMBER       PATH '$.qty',
               price   NUMBER       PATH '$.price'
             )
         )) jt;

SELECT order_id, customer_name, qty
  FROM order_items_v
 WHERE sku = 'KB-101'
 ORDER BY order_id;
A relational view over JSON documents
ORDER_ID CUSTOMER_NAME  QTY
-------- -------------- ----
       1 Asha Rao          2
       4 Rahul Verma
Output (expected output)

Order 4 appears with a NULL quantity, the same silent NULL from Example 5. If the JSON structure changes later, you only redefine the view. The stored documents stay as they are.

Common mistakes

Wrong case in the path

Path matching is case-sensitive. '$.Customer.Name' does not match a field called name, and with the default NULL ON ERROR you just get NULL. Copy field names exactly from a sample document.

Losing parent rows

Using the array as the row path, or a comma join when the document itself is NULL, drops the parent row. If a document is NULL, JSON_TABLE returns no rows for it. To keep every order, use '

Home
SQL
PLSQL
Interview Q&A
with NESTED PATH, or the form the documentation recommends: FROM orders o LEFT OUTER JOIN JSON_TABLE(o.order_doc, ...) jt ON 1 = 1.

Trusting the default error handling

NULL ON ERROR is convenient for exploring data and dangerous for totals. Use ERROR ON ERROR, or the two-column check from Example 5, before you build reports on a new feed.

Counting with COUNT(*) after NESTED PATH

An order with an empty array still produces one row with NULL item columns. COUNT(*) counts that row. Count a nested column, such as COUNT(jt.sku), instead.

Forgetting ORDER BY

Like any query, JSON_TABLE output has no guaranteed order without ORDER BY. Sort by the parent key and the ordinality column when item order matters.

Performance tips

Check the plan with DBMS_XPLAN to confirm what Oracle does. Our guide on how to read an explain plan in Oracle covers that step.

Frequently asked questions

What is JSON_TABLE in Oracle?

It is a SQL function used in the FROM clause that projects JSON data as a virtual relational table. You give it a JSON expression, a row path and a list of typed columns, and it returns rows you can join, filter and aggregate.

Which Oracle version supports JSON_TABLE?

JSON_TABLE arrived with Oracle's SQL/JSON support in 12c Release 1 (12.1.0.2). The examples here use 19c syntax, which also runs in 21c and 23ai. Oracle 21c added the native JSON data type, and JSON_TABLE also reads VARCHAR2, CLOB and BLOB columns (in 19c a BLOB column needs FORMAT JSON after it).

How do I read a nested array with JSON_TABLE?

Add NESTED PATH '$.array_name[*]' with its own COLUMNS clause. Each element becomes a row, parent columns repeat, and parents with an empty array are kept with NULLs. Nested arrays inside the array can have their own NESTED PATH, since the clause is recursive.

What is the difference between JSON_VALUE and JSON_TABLE?

JSON_VALUE returns one scalar value per call and is used in the SELECT list or WHERE clause. JSON_TABLE returns many columns, and many rows for arrays, from one call. Use JSON_VALUE for one or two fields, and JSON_TABLE when you need several fields or array elements.

Can I use JSON_TABLE in PL/SQL?

Yes. The first argument can be a PL/SQL variable, so you can loop over a JSON string you received without storing it in a table:

DECLARE
  l_doc CLOB := '{"items":[{"sku":"KB-101","qty":2},
                           {"sku":"MS-220","qty":1}]}';
BEGIN
  FOR r IN (SELECT jt.sku, jt.qty
              FROM JSON_TABLE(l_doc, '$.items[*]'
                     COLUMNS (sku VARCHAR2(20) PATH '$.sku',
                              qty NUMBER       PATH '$.qty')) jt)
  LOOP
    DBMS_OUTPUT.PUT_LINE(r.sku || ' x ' || r.qty);
  END LOOP;
END;
/
JSON_TABLE over a PL/SQL variable
KB-101 x 2
MS-220 x 1
Output (expected output)

Summary

PT Ganesh

An experienced PostgreSQL DBA/SQL developer with years of expertise in designing and implementing robust database solutions. Proficient in writing complex queries, developing stored procedures, and conducting performance tuning to deliver highly efficient data-driven applications that meet business requirements.

Read more about PT Ganesh

Home
SQL
PLSQL
Interview Q&A