JSON_TABLE
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some dialects ship with a built-in standard SQL table-valued function called JSON_TABLE
, which can be used to unnest a JSON data structure into a SQL table.
SELECT * FROM json_table( '[{"a":5,"b":{"x":10}},{"a":7,"b":{"y":20}}]', '$[*]' COLUMNS ( id FOR ORDINALITY, a INT, x INT PATH '$.b.x', y INT PATH '$.b.y' ) ) AS t
create.select() .from(jsonTable( json("[{\"a\":5,\"b\":{\"x\":10}}," + "{\"a\":7,\"b\":{\"y\":20}}]"), "$[*]") .column("id").forOrdinality() .column("a", INTEGER) .column("x", INTEGER).path("$.b.x") .column("y", INTEGER).path("$.b.y") .as("t")) .fetch();
The result would look like this:
+----+---+----+----+ | ID | A | X | Y | +----+---+----+----+ | 1 | 5 | 10 | | | 2 | 7 | | 20 | +----+---+----+----+
Dialect support
This example using jOOQ:
selectFrom(jsonTable(json("[{\"a\":5,\"b\":{\"x\":10}}]"), "$[*]").column("id").forOrdinality().column("a", INTEGER).column("x", INTEGER).path("$.b.x").as("t"))
Translates to the following dialect specific expressions:
DB2
SELECT t.id, t.a, t.x FROM JSON_TABLE( '[{"a":5,"b":{"x":10}}]', '$[*]' COLUMNS ( id FOR ORDINALITY, a integer, x integer PATH '$.b.x' ) ERROR ON ERROR ) t
Hana
SELECT t.id, t.a, t.x FROM JSON_TABLE( '[{"a":5,"b":{"x":10}}]', '$[*]' COLUMNS ( id FOR ORDINALITY, a integer PATH '$.a', x integer PATH '$.b.x' ) ) t
MariaDB, MySQL
SELECT t.id, t.a, t.x FROM JSON_TABLE( '[{"a":5,"b":{"x":10}}]', '$[*]' COLUMNS ( id FOR ORDINALITY, a int PATH '$.a', x int PATH '$.b.x' ) ) t
Oracle
SELECT t.id, t.a, t.x FROM JSON_TABLE( '[{"a":5,"b":{"x":10}}]', '$[*]' COLUMNS ( id FOR ORDINALITY, a number(10), x number(10) PATH '$.b.x' ) ) t
Postgres, YugabyteDB
SELECT t.id, t.a, t.x FROM ( SELECT o id, cast((jsonb_path_query_first(j, cast('$.a' as jsonpath))->>0) as INT) a, cast((jsonb_path_query_first(j, cast('$.b.x' as jsonpath))->>0) as INT) x FROM jsonb_path_query(CAST('[{"a":5,"b":{"x":10}}]' AS jsonb), cast('$[*]' as jsonpath)) WITH ORDINALITY AS t(j, o) ) t
SQLServer
SELECT t.id, t.a, t.x FROM ( SELECT row_number() OVER (ORDER BY ( SELECT 1 )) id, a, x FROM openjson('[{"a":5,"b":{"x":10}}]', '$[*]') WITH ( a int, x int '$.b.x' ) t ) t
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!