Oracle JSON_TABLE Function

The JSON_TABLE function in Oracle SQL is the most powerful and complex of Oracle's JSON functions. Its job is to "shred" a JSON document and present it as a regular relational table, with the JSON data split into proper rows and columns.

While JSON_VALUE gets one scalar value and JSON_QUERY gets one JSON fragment, JSON_TABLE is used in the FROM clause of your query to turn a JSON array of objects into a set of rows.

What is the JSON_TABLE Function in Oracle?

The JSON_TABLE function is a "JSON-to-table" converter. You provide it with JSON data, and it returns a virtual table that you can select from.

This is essential for:

• Converting a JSON array of items (like a list of products) into multiple rows in a result set.
• Extracting multiple values from a JSON object into separate, strongly-typed SQL columns (e.g., NUMBER, VARCHAR2, DATE).
• "Un-nesting" or "flattening" complex nested JSON data into a simple, flat table.

JSON_TABLE Function Syntax

The syntax for JSON_TABLE can be very complex, but for 90% of use cases, you only need this "shredding" pattern:

JSON_TABLE(
    json_expr,
    row_path_expression
    COLUMNS (
        column_name1 data_type PATH path_expression1,
        column_name2 data_type PATH path_expression2,
        ...
    )
)

Let's break that down:

• json_expr: The JSON data (e.g., a CLOB column or, for our examples, a text string).
• row_path_expression: This is the most important part. It's a SQL/JSON path that tells the function which part of the JSON document represents a single row. This is almost always a path to an array, ending in [*].
  • Example: $.items[*] means "each object in the 'items' array is a new row."
• COLUMNS ( ... ): This clause defines the new SQL columns you want to create.
• column_name data_type PATH path_expression:
  • column_name: The name of your new SQL column (e.g., product_name).
  • data_type: The SQL data type for this new column (e.g., VARCHAR2(30) or NUMBER).
  • PATH path_expression: The path inside the row object to get the value for this column (e.g., $.product).

Oracle JSON_TABLE Function Examples

The following examples use JSON_TABLE in the FROM clause. This replaces the need for the DUAL table.

Example 1: Converting a JSON Array to Rows using JSON_TABLE

This is the most common use case. We have a JSON string containing an array of "items". We want to turn this array into a table with two rows, each having a prod_id and prod_name column.

Query:

SELECT
  jt.prod_id,
  jt.prod_name
FROM
  JSON_TABLE(
    '{"items": [{"id": 1, "product": "Widget"}, {"id": 2, "product": "Gadget"}]}',
    '$.items[*]' -- This path creates the rows (one for each array element)
    COLUMNS (
      prod_id    NUMBER       PATH '$.id',       -- Extracts 'id' as a NUMBER
      prod_name  VARCHAR2(20) PATH '$.product' -- Extracts 'product' as a VARCHAR2
    )
  ) jt; -- 'jt' is the alias for our new virtual table

Result: (The JSON array has been successfully shredded into a 2-row, 2-column relational table.)

   PROD_ID PROD_NAME
---------- --------------------
         1 Widget
         2 Gadget

Example 2: Un-nesting a Complex Object using JSON_TABLE

This example shows how to pull "parent" level data (like PONumber) and "flatten" a nested array (LineItems) at the same time. The result will repeat the parent data for each line item.

Query:

SELECT
  jt.po_number,
  jt.requestor,
  jt.item_num,
  jt.item_desc
FROM
  JSON_TABLE(
    '{"PONumber": 1600, "Requestor": "Alexis Bull", "LineItems": [
      {"ItemNumber": 1, "Description": "One Magic Christmas"},
      {"ItemNumber": 2, "Description": "Lethal Weapon"}
    ]}',
    '$' -- The main path is the whole document (one "parent" row)
    COLUMNS (
      po_number NUMBER PATH '$.PONumber', -- Get parent data
      requestor VARCHAR2(20) PATH '$.Requestor', -- Get parent data
      
      -- This "joins" to the inner array
      NESTED PATH '$.LineItems[*]' 
        COLUMNS (
          item_num  NUMBER       PATH '$.ItemNumber', -- Get child data
          item_desc VARCHAR2(20) PATH '$.Description' -- Get child data
        )
    )
  ) jt;

Result: (The query successfully flattened the data, creating two rows from the one JSON document.)

 PO_NUMBER REQUESTOR            ITEM_NUM ITEM_DESC
---------- -------------------- ---------- --------------------
      1600 Alexis Bull                   1 One Magic Christmas
      1600 Alexis Bull                   2 Lethal Weapon