PATH模式
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
PATH 模式根据列别名指定的“路径”生成 JSON 内容。
考虑以下查询
SELECT id AS [book.id], title AS [book.title] FROM book ORDER BY id FOR JSON PATH
create.select(
BOOK.ID.as("book.id"),
BOOK.TITLE.as("book.title"))
.from(BOOK)
.orderBy(BOOK.ID)
.forJSON().path()
.fetch();
此查询生成如下文档
[
{"book":{"id":1,"title":"1984"}},
{"book":{"id":2,"title":"Animal Farm"}},
{"book":{"id":3,"title":"O Alquimista"}},
{"book":{"id":4,"title":"Brida"}}
]
方言支持
此示例使用 jOOQ
select(BOOK.ID.as(quotedName("book.id"))).from(BOOK).orderBy(BOOK.ID).forJSON().path()
翻译成以下特定方言的表达式
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
SELECT json_agg(json_strip_nulls(json_build_object('book', json_strip_nulls(json_build_object('id', "book.id")))))
FROM (
SELECT BOOK.ID "book.id"
FROM BOOK
ORDER BY BOOK.ID
) t
DB2
SELECT CAST(('[' || listagg(
json_object(
KEY 'book' VALUE json_object(
KEY 'id' VALUE "book.id"
ABSENT ON NULL
) FORMAT JSON
ABSENT ON NULL
),
','
) || ']') AS varchar(32672))
FROM (
SELECT BOOK.ID "book.id"
FROM BOOK
ORDER BY BOOK.ID
) t
H2
SELECT json_arrayagg(json_object(
KEY 'book' VALUE json_object(
KEY 'id' VALUE "book.id"
ABSENT ON NULL
)
ABSENT ON NULL
))
FROM (
SELECT BOOK.ID "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('book', json_object('id', `book.id`)) SEPARATOR ','),
']'
)
)
FROM (
SELECT BOOK.ID `book.id`
FROM BOOK
ORDER BY BOOK.ID
) t;
SET @@group_concat_max_len = @t;
Oracle
SELECT json_arrayagg(json_object(
KEY 'book' VALUE json_object(
KEY 'id' VALUE "book.id"
ABSENT ON NULL
RETURNING clob
) FORMAT JSON
ABSENT ON NULL
RETURNING clob
) FORMAT JSON RETURNING clob)
FROM (
SELECT BOOK.ID "book.id"
FROM BOOK
ORDER BY BOOK.ID
) t
SQLServer
SELECT ( SELECT BOOK.ID [book.id] FROM BOOK ORDER BY BOOK.ID FOR JSON PATH )
Trino
SELECT cast(array_agg(CAST(map_from_entries(filter(
ARRAY[row(
'book',
CAST(CAST(map_from_entries(filter(
ARRAY[row(
'id',
CAST("book.id" AS json)
)],
e -> e[2] IS NOT NULL
)) AS json) AS json)
)],
e -> e[2] IS NOT NULL
)) AS json)) AS json)
FROM (
SELECT BOOK.ID "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
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！