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, Databricks, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!