JSON_TABLE
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
一些方言带有内置的标准 SQL 表值函数，名为 JSON_TABLE，可用于将 JSON 数据结构解构为 SQL 表。
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();
结果如下
+----+---+----+----+ | ID | A | X | Y | +----+---+----+----+ | 1 | 5 | 10 | | | 2 | 7 | | 20 | +----+---+----+----+
方言支持
此示例使用 jOOQ
selectFrom(jsonTable(json("[{\"a\":5,\"b\":{\"x\":10}}]"), "$[*]").column("id").forOrdinality().column("a", INTEGER).column("x", INTEGER).path("$.b.x").as("t"))
翻译成以下特定方言的表达式
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 */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意倾听！