JSON_TABLEJSON
Buy Me A Coffee

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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0.4 Added in MySQL 8.0.4
PostgreSQL ✓ Supported 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 2016 Use OPENJSON
Oracle ✓ Supported 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: Since 8.0.4 PostgreSQL: Since 17 SQL Server: Since 2016 Oracle: Since 12c SQLite: Not supported

Per-Database Syntax & Notes

MySQL

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 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

Use OPENJSON

SELECT * FROM tbl CROSS APPLY OPENJSON(json_col) WITH (name NVARCHAR(50) '$.name', age INT '$.age') AS jt;

Oracle

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

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);