SQL JSON_TABLE

What is SQL JSON_TABLE?

JSON_TABLE turns JSON data into a relational table that can be queried with standard SQL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL JSON_TABLE: Oracle 12.2+, MySQL 8.0+, MariaDB 10.6+, IBM Db2 11.5+, SQL Server 2022, Trino/Presto (as json_table), Databricks SQL

SQL JSON_TABLE Full Explanation

JSON_TABLE is an inline table-valued function defined in SQL:2016 and implemented by several vendors. It parses a JSON document and projects selected elements into virtual columns and rows, effectively "shredding" the hierarchical JSON into a relational format. The function is usually used in the FROM clause, where it behaves like a derived table or lateral view that can be joined, filtered, or aggregated. Each column definition specifies a JSON path expression and an optional data type, default value, or ON ERROR/ON EMPTY behavior. When the supplied JSON path points to an array, JSON_TABLE returns one row per array element, enabling set-based processing. Because the output is computed at query time, no persistent table is created; performance therefore depends on the engine's JSON parsing efficiency and indexing support. Caveats: invalid JSON raises errors unless IS JSON or error-handling clauses are used; path expressions are case-sensitive in some dialects; large documents may require dedicated JSON indexes for acceptable speed.

SQL JSON_TABLE Syntax

JSON_TABLE (
  json_expr,
  'json_path' [ PASSING { expr AS identifier } [, ...] ]
  COLUMNS (
    column_name data_type [ FORMAT JSON ] [ PATH 'col_path' ]
      [ DEFAULT default_expr ] [ ON ERROR clause ] [ ON EMPTY clause ],
    ...
  )
)

SQL JSON_TABLE Parameters

json_expr - any expression that yields a JSON document or string
'json_path' - root JSON path that selects the portion to be projected
PASSING - optional list of bind variables to reference inside path expressions
COLUMNS - mandatory list that defines output columns
column_name - identifier for the virtual column
data_type (target SQL type) - INT, VARCHAR, DATE, etc.
PATH - JSON path relative to the root path that extracts the value for the column
DEFAULT - fallback value if the path is missing or empty
ON ERROR / ON EMPTY - error handling options (NULL, DEFAULT, ERROR)

Example Queries Using SQL JSON_TABLE

-- Example 1: Flatten a simple JSON column
SELECT jt.*
FROM orders o,
     JSON_TABLE(o.order_json,
       '$'
       COLUMNS (
         order_id   INT          PATH '$.id',
         created_at TIMESTAMP    PATH '$.created',
         customer   VARCHAR(50)  PATH '$.customer.name',
         status     VARCHAR(20)  PATH '$.status'
       )
     ) jt;

-- Example 2: One row per line item in an array
SELECT o.id AS order_id, li.product_id, li.qty
FROM orders o,
     JSON_TABLE(o.order_json,
       '$.items[*]'
       COLUMNS (
         product_id INT PATH '$.sku',
         qty        INT PATH '$.quantity'
       )
     ) li;

Expected Output Using SQL JSON_TABLE

Example 1 returns one row per row in ORDERS with four projected columns sourced from the JSON
Example 2 returns multiple rows per order: one for every element inside the items array, exposing PRODUCT_ID and QTY that can be joined or aggregated like regular columns

Use Cases with SQL JSON_TABLE

Query nested JSON stored in a column without loading it into a staging table
Join relational data with attributes inside a JSON payload
Aggregate or filter array elements (e.g., order line items, event logs)
Migrate JSON data to relational tables during ETL
Build views that expose JSON documents as first-class relational tables

Common Mistakes with SQL JSON_TABLE

Forgetting to alias JSON_TABLE, causing column name conflicts
Supplying an invalid JSON path, which returns NULL or raises ERROR depending on settings
Ignoring array semantics: omitting the [*] wildcard yields only the first element
Expecting JSON_TABLE to create a permanent table; it is only a runtime view
Using incorrect data types, leading to implicit casts or runtime errors

First Introduced In

SQL:2016; first commercial support in Oracle Database 12c Release 2

Frequently Asked Questions

What is the difference between JSON_TABLE and JSON_VALUE?

JSON_VALUE extracts a single scalar value. JSON_TABLE can return multiple columns and rows, making it suitable for set-based operations.

How do I handle missing JSON keys safely?

Add ON ERROR NULL or DEFAULT clauses to each column definition, or validate the document with IS JSON before calling JSON_TABLE.

Can JSON_TABLE work with arrays of objects?

Yes. Use the path '$.array[*]' to iterate over each element, and define columns that map to properties inside the object.

Does JSON_TABLE hurt performance on large documents?

Parsing large JSON can be expensive. Create vendor-specific JSON indexes or persist the shredded data into a table for high-traffic workloads.