jOOQ在Oracle中使用coalesce()与format json的渲染问题

问题描述 #

在Oracle 21c环境使用jOOQ时，尝试将coalesce()与format json结合并嵌套进DSL.jsonEntry()时，jOOQ会自动为表达式追加as varchar2(4000)，导致无法生成预期的SQL语句。

预期SQL输出：

key ? value coalesce("foo", to_clob('{ "rows": [] }')) format json

重现代码 #

import org.jooq.SQLDialect
import org.jooq.impl.DSL
import org.jooq.impl.SQLDataType

val dsl = DSL.using(SQLDialect.ORACLE21C)

val stmt1 = DSL.field(
    "{0} format json",
    DSL.coalesce(
        DSL.field(DSL.name("foo"), SQLDataType.CLOB),
        DSL.cast(DSL.inline("{ \"rows\": [] }"), SQLDataType.CLOB)
    )
)

val composed1 = DSL.jsonEntry("key", stmt1)

// 实际渲染结果：key ? value cast(coalesce("foo", to_clob('{ "rows": [] }')) format json as varchar2(4000))
println("RENDERED 3: ${dsl.render(composed1)}")

问题原因 #

jOOQ在处理jsonEntry()时，会默认将传入的字段转换为字符串类型（Oracle下默认映射为varchar2(4000)），即使原字段已经带有format json修饰符，也会被强制包裹进cast(...) as varchar2(4000)结构中，破坏了预期的SQL逻辑。

解决方案 #

要避免自动类型转换，需要明确告诉jOOQ当前表达式的目标类型为SQLDataType.JSON，以下两种方式均可实现：

方式1：显式指定字段返回类型为JSON #

修改stmt1的定义，在DSL.field()中直接声明返回类型为JSON：

val stmtFixed = DSL.field(
    "{0} format json",
    SQLDataType.JSON, // 显式指定返回类型
    DSL.coalesce(
        DSL.field(DSL.name("foo"), SQLDataType.CLOB),
        DSL.cast(DSL.inline("{ \"rows\": [] }"), SQLDataType.CLOB)
    )
)

val composedFixed = DSL.jsonEntry("key", stmtFixed)
// 渲染结果符合预期：key ? value coalesce("foo", to_clob('{ "rows": [] }')) format json
println("RENDERED FIXED: ${dsl.render(composedFixed)}")

方式2：用DSL.json()包裹coalesce表达式 #

使用jOOQ提供的DSL.json()函数包装coalesce结果，让框架识别其JSON类型：

val stmtFixed2 = DSL.json(
    DSL.coalesce(
        DSL.field(DSL.name("foo"), SQLDataType.CLOB),
        DSL.cast(DSL.inline("{ \"rows\": [] }"), SQLDataType.CLOB)
    )
)

val composedFixed2 = DSL.jsonEntry("key", stmtFixed2)
// 渲染结果：key ? value json(coalesce("foo", to_clob('{ "rows": [] }'))) format json
println("RENDERED FIXED 2: ${dsl.render(composedFixed2)}")

说明：方式1完全匹配预期的SQL输出；方式2使用Oracle原生json()函数，同样能实现正确的JSON处理逻辑，且更符合jOOQ的类型安全设计规范。

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