ROOT指令
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
ROOT 指令允许将 JSON 文档包装在根对象中。
考虑以下查询
SELECT id, title
FROM book
ORDER BY id
FOR JSON AUTO, ROOT ('result')
create.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.orderBy(BOOK.ID)
.forJSON().auto().root("result")
.fetch();
此查询生成如下文档
{
"result": [
{"id": 1, "title": "1984"},
{"id": 2, "title": "Animal Farm"},
{"id": 3, "title": "O Alquimista"},
{"id": 4, "title": "Brida"}
]
}
方言支持
此示例使用 jOOQ
select(BOOK.ID).from(BOOK).orderBy(BOOK.ID).forJSON().auto().root("result")
翻译成以下特定方言的表达式
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
SELECT json_strip_nulls(json_build_object('result', json_agg(json_strip_nulls(json_build_object('ID', ID)))))
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
DB2
SELECT json_object(
KEY 'result' VALUE CAST(('[' || listagg(
json_object(
KEY 'ID' VALUE ID
ABSENT ON NULL
),
','
) || ']') AS varchar(32672)) FORMAT JSON
ABSENT ON NULL
)
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
H2
SELECT json_object(
KEY 'result' VALUE json_arrayagg(json_object(
KEY 'ID' VALUE ID
ABSENT ON NULL
))
ABSENT ON NULL
)
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
MariaDB
SET @t = @@group_concat_max_len;
SET @@group_concat_max_len = 4294967295;
SELECT json_object('result', json_merge_preserve(
'[]',
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_object('ID', ID) SEPARATOR ','),
']'
)
)
))
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t;
SET @@group_concat_max_len = @t;
MySQL
SET @t = @@group_concat_max_len;
SET @@group_concat_max_len = 4294967295;
SELECT json_object('result', json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_object('ID', ID) SEPARATOR ','),
']'
)
))
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t;
SET @@group_concat_max_len = @t;
Oracle
SELECT json_object(
KEY 'result' VALUE json_arrayagg(json_object(
KEY 'ID' VALUE ID
ABSENT ON NULL
RETURNING clob
) FORMAT JSON RETURNING clob) FORMAT JSON
ABSENT ON NULL
RETURNING clob
)
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
SQLServer
SELECT (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
FOR JSON AUTO, ROOT ('result')
)
Trino
SELECT CAST(map_from_entries(filter(
ARRAY[row(
'result',
CAST(cast(array_agg(CAST(map_from_entries(filter(
ARRAY[row(
'ID',
CAST(ID AS json)
)],
e -> e[2] IS NOT NULL
)) AS json)) AS json) AS json)
)],
e -> e[2] IS NOT NULL
)) AS json)
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Databricks, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Vertica
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听到！