MariaDB JSON_TABLE(): Convert JSON to Rows with Examples

MariaDB JSON_TABLE() converts JSON data into a relational table that can be queried in a FROM clause. It is available in MariaDB 10.6 and later.

This is useful when JSON arrives as an array or object but downstream SQL needs ordinary rows and typed columns.

Syntax

JSON_TABLE(
    json_doc,
    context_path COLUMNS (
        column_definition,
        ...
    )
) AS alias

Common column definitions are:

name FOR ORDINALITY
name type PATH path_string [on_empty] [on_error]
name type EXISTS PATH path_string
NESTED PATH path_string COLUMNS (...)

Example: convert a JSON array to rows

SELECT jt.id, jt.name, jt.price
FROM JSON_TABLE(
    '[
       {"name":"Laptop","price":1000},
       {"name":"Mouse","price":25}
     ]',
    '$[*]' COLUMNS (
        id FOR ORDINALITY,
        name VARCHAR(100) PATH '$.name',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt;

Result:

+----+--------+---------+
| id | name   | price   |
+----+--------+---------+
|  1 | Laptop | 1000.00 |
|  2 | Mouse  |   25.00 |
+----+--------+---------+

The context path $[*] selects each array element as a source row.

Handle missing values with ON EMPTY

When a path does not exist, NULL ON EMPTY is the default. You can supply another behavior:

SELECT *
FROM JSON_TABLE(
    '[{"name":"A","qty":2},{"name":"B"}]',
    '$[*]' COLUMNS (
        name VARCHAR(20) PATH '$.name',
        qty INT PATH '$.qty' DEFAULT '0' ON EMPTY
    )
) AS jt;

The second row receives 0 for qty.

Available choices include:

NULL ON EMPTY
DEFAULT ... ON EMPTY
ERROR ON EMPTY

ON ERROR provides corresponding behavior when a JSON value cannot be extracted as requested.

Test whether a path exists

Use EXISTS PATH to return a 1/0 flag:

SELECT *
FROM JSON_TABLE(
    '[{"name":"A","price":10},{"name":"B"}]',
    '$[*]' COLUMNS (
        name VARCHAR(20) PATH '$.name',
        has_price INT EXISTS PATH '$.price'
    )
) AS jt;

This is useful when “missing” and “present with a value” need different treatment.

Flatten nested arrays

SELECT jt.name, jt.tag
FROM JSON_TABLE(
    '[
       {"name":"A","tags":["new","sale"]},
       {"name":"B","tags":["clearance"]}
     ]',
    '$[*]' COLUMNS (
        name VARCHAR(20) PATH '$.name',
        NESTED PATH '$.tags[*]' COLUMNS (
            tag VARCHAR(20) PATH '$'
        )
    )
) AS jt;

Result:

+------+-----------+
| name | tag       |
+------+-----------+
| A    | new       |
| A    | sale      |
| B    | clearance |
+------+-----------+

Where JSON_TABLE() can be used

Because JSON_TABLE() behaves like a table reference, it can participate in normal SQL operations such as joins and filtering after the JSON values have been projected into columns.

Common mistakes

Forgetting the table alias

Use an alias such as AS jt after JSON_TABLE(...).

Confusing JSON_TABLE() with scalar JSON functions

JSON_VALUE()-style operations extract a value. JSON_TABLE() creates a rowset with columns.

Assuming ON ERROR catches every SQL conversion warning

MariaDB distinguishes JSON structure errors from ordinary datatype conversion issues. Review warnings when converting extracted text into numeric or other typed columns.