如何在Jooq中使用PostgreSQL的jsonb_array_elements函数转换查询？

完整转换后的jOOQ代码（基于生成的元数据类） #

假设你已经通过jOOQ代码生成器生成了Shipment（别名S）和SalesOrder（别名SO）的元数据类，以下是对应原SQL的jOOQ实现：

import static org.jooq.impl.DSL.*;

// 执行查询并获取结果
Result<Record4<Long, Long, JSONB, JSONB>> result = ctx.select(
        S.ID.as("shipment_id"),
        SO.ID.as("sales_order_id"),
        // 类型安全的JSON字段提取方式
        field("{0}", JSONB.class, "s_item").get("productId").as("product_id"),
        field("{0}", JSONB.class, "s_item").get("sub_items").as("sub_items")
    )
    .from(S)
    .join(SO).on(S.SALES_ORDER_ID.eq(SO.ID))
    // 使用jOOQ内置方法对应jsonb_array_elements
    .crossJoin(lateral(jsonbArrayElements(S.ITEMS)).as("s_item"))
    .where(S.STATUS.eq("READY"))
    .fetch();

两种实现方式说明 #

1. 内置方法（jOOQ 3.14+ 推荐）

jOOQ 3.14及以上版本原生支持jsonbArrayElements()方法，直接对应PostgreSQL的jsonb_array_elements函数。由于该函数返回多行结果，需要配合lateral()进行横向连接，对应原SQL中的隐式交叉横向连接（逗号分隔表的写法）。

2. 原生SQL片段兼容（全版本通用）

如果你的jOOQ版本较低，没有内置的jsonbArrayElements()，可以直接构造原生SQL片段来调用函数：

// 替换crossJoin部分的代码
.crossJoin(lateral(field("jsonb_array_elements({0})", JSONB.class, S.ITEMS)).as("s_item"))

JSON字段提取的优化 #

原SQL中的s_item -> 'productId'语法，除了用原生片段，还可以用jOOQ提供的类型安全API：

// 先将s_item转换为JSONB类型，再提取字段
field("{0}", JSONB.class, "s_item").get("productId")

这种方式避免了硬编码SQL片段，更符合jOOQ的类型安全设计。

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