如何使用JOOQ和PostgreSQL返回JSON对象数组而不是行集合

Question

阅读了这篇文章，文章建议使用JSON运算符直接从数据库返回JSON有时是一个很好的折衷方案；我正在使用PostgreSQL和JOOQ来探索这个想法。

我能够使用以下模式编写返回JSON对象的JSON数组而不是行的SQL查询：

select jsonb_pretty(array_to_json(array_agg(row_to_json(r)))::jsonb)
from (
select [subquery]
) as r;

然而，我没能在JOOQ中翻译这种模式。

关于如何使用JOOQ翻译行集合(字段为"常用"SQL类型或已映射为json(b((的任何帮助都将不胜感激。

Answer 1

SQL ServerFOR JSON语义

这正是SQL ServerFOR JSON子句所做的，jOOQ支持它，它也可以在其他方言上为您模拟它：

ctx.select(T.A, T.B)
.from(T)
.forJSON().path()
.fetch();

PostgreSQL本机功能

如果您更喜欢直接使用本机函数，那么现在就必须使用纯SQL模板，因为jOOQ还不支持其中一些函数，包括：

• JSONB_PRETTY(还没有支持它的计划(
• ARRAY_TO_JSON(https://github.com/jOOQ/jOOQ/issues/12841)
• ROW_TO_JSON(https://github.com/jOOQ/jOOQ/issues/10685)

编写一个能做到这一点的实用程序似乎很简单：

public static ResultQuery<Record1<JSONB>> json(Select<?> subquery) {
return ctx
.select(field(
"jsonb_pretty(array_to_json(array_agg(row_to_json(r)))::jsonb)", 
JSONB.class
))
.from(subquery.asTable("r"))
}

现在，您可以以您想要的形式执行任何查询：

JSONB result = ctx.fetchValue(json(select(T.A, T.B).from(T)));

PG数组和JSON数组之间的转换

关于表演的说明。您似乎经常在数据类型之间进行转换。具体来说，我建议您避免聚合PostgreSQL数组并将其转换为JSON数组，而是直接使用JSONB_AGG()。我还没有对此进行测试，但在我看来，额外的数据结构似乎没有必要。