如何用jOOQ 3.15.5编写含jsonb_array_elements聚合的查询

以下是对应你提供的SQL的jOOQ实现代码，基于生成的Runs.RUNS表结构：

import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.impl.DSL;
import java.math.BigDecimal;
import static com.your.generated.Runs.RUNS;

public class InvoiceAggregationQuery {
    public void execute(DSLContext dsl) {
        // 定义JSONB数组展开后的表别名j
        Field<org.jooq.JSONB> j = DSL.jsonbArrayElements(RUNS.INVOICES).as("j");
        
        var result = dsl.select(
                RUNS.ORGANISATION_ID,
                // 统计非null的invoiceId数量
                DSL.count(j.extract("invoiceId").asString()).as("count"),
                // 求和并转换为BigDecimal类型
                DSL.sum(j.extract("advanceAmount").asString().cast(BigDecimal.class)).as("sum")
            )
            .from(RUNS)
            .leftJoin(DSL.table(j))
            .on(DSL.trueCondition())
            .groupBy(RUNS.ORGANISATION_ID)
            .fetch();
        
        // 处理查询结果
        // ...
    }
}

关键部分说明 #

• JSON数组展开：DSL.jsonbArrayElements(RUNS.INVOICES)对应SQL中的jsonb_array_elements(invoices)，通过as("j")设置别名，和原SQL保持一致。
• JSON字段提取：j.extract("invoiceId").asString()等价于SQL的j->>'invoiceId'，extract方法用于指定JSON路径提取值，asString()将其转为字符串类型。
• 类型转换：cast(BigDecimal.class)对应SQL的::numeric(20,4)，jOOQ会自动将其映射为PostgreSQL的numeric类型，匹配Java的BigDecimal。
• 左连接条件：on(DSL.trueCondition())对应原SQL的on true，确保所有主表记录都被保留。

内容的提问来源于stack exchange，提问作者Pedro Borges