AUTO模式
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
AUTO 模式基于自动生成的对象键生成 JSON 内容，这些对象键模拟查询结构。
考虑以下查询
SELECT id, title FROM book ORDER BY id FOR JSON AUTO
create.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.orderBy(BOOK.ID)
.forJSON().auto()
.fetch();
此查询生成如下文档
[
{"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()
翻译成以下特定方言的表达式
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
SELECT json_agg(json_strip_nulls(json_build_object('ID', ID)))
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
DB2
SELECT CAST(('[' || listagg(
json_object(
KEY 'ID' VALUE ID
ABSENT ON NULL
),
','
) || ']') AS varchar(32672))
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID
) t
H2
SELECT json_arrayagg(json_object( KEY 'ID' VALUE ID ABSENT ON NULL )) FROM ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID ) t
MariaDB, MySQL
SET @t = @@group_concat_max_len;
SET @@group_concat_max_len = 4294967295;
SELECT 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_arrayagg(json_object( KEY 'ID' VALUE ID ABSENT ON NULL RETURNING clob ) FORMAT JSON 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 )
Sybase
SELECT ( SELECT BOOK.ID FROM BOOK ORDER BY BOOK.ID FOR JSON AUTO ) FROM SYS.DUMMY
Trino
SELECT 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)
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, Teradata, Vertica
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！