Extracts JSON data and returns it as a relational table.
MySQL 8.0 suite of JSON functions including JSON_TABLE, which converts JSON to a relational rowset, and JSON_OBJECTAGG / JSON_ARRAYAGG for aggregation. JSON_TABLE enables powerful SQL-over-JSON queries.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 8.0.4 | Added in MySQL 8.0.4 |
| PostgreSQL | Native | 17 | Native JSON_TABLE support added in PostgreSQL 17. Prior versions use jsonb_to_recordset() or lateral jsonb_array_elements() as workarounds. |
| SQL Server | Supported via workaround | - | Workaround since 2016 / compatibility level 130. No native JSON_TABLE support. Use OPENJSON for equivalent rowset extraction from JSON. |
| Oracle | Native | 12c | Native JSON_TABLE |
| SQLite | Not Supported | - | No JSON_TABLE—use json_each/json_extract |
Details
Turn JSON docs into relational rows/columns for easy querying.
Standard Syntax
SELECT jt.*
FROM my_table,
JSON_TABLE(json_col, '$' COLUMNS (
name VARCHAR(100) PATH '$.name',
age INT PATH '$.age'
)) AS jt;
Version Support
MySQL: Native since 8.0.4
PostgreSQL: Native since 17
SQL Server: Supported via workaround since 2016 / compatibility level 130
Oracle: Native since 12c
SQLite: Not supported
Per-Database Syntax & Notes
MySQL Native syntax
Added in MySQL 8.0.4
SELECT jt.*
FROM tbl,
JSON_TABLE(json_col, '$' COLUMNS (
name VARCHAR(50) PATH '$.name',
age INT PATH '$.age'
)) AS jt;
PostgreSQL Native syntax
Native JSON_TABLE support added in PostgreSQL 17. Prior versions use jsonb_to_recordset() or lateral jsonb_array_elements() as workarounds.
SELECT jt.*
FROM tbl,
JSON_TABLE(json_col, '$[*]' COLUMNS (
name TEXT PATH '$.name',
age INT PATH '$.age'
)) AS jt;
SQL Server Workaround
Use OPENJSON with CROSS APPLY to project JSON values as rows and columns. Since: 2016 / compatibility level 130. No native JSON_TABLE support. Use OPENJSON for equivalent rowset extraction from JSON.
SELECT *
FROM tbl
CROSS APPLY OPENJSON(json_col)
WITH (name NVARCHAR(50) '$.name', age INT '$.age') AS jt;
Oracle Native syntax
Native JSON_TABLE
SELECT jt.name, jt.age
FROM tbl,
JSON_TABLE(json_col, '$'
COLUMNS (name VARCHAR2(50) PATH '$.name', age NUMBER PATH '$.age')
) jt;
SQLite Alternative syntax
No JSON_TABLE—use json_each/json_extract
SELECT
json_extract(value, '$.name') AS name,
json_extract(value, '$.age') AS age
FROM tbl, json_each(json_col);