jOOQ查询jsonb列包含指定值未正常生效问题

你写的查询不生效的核心原因是：直接传入Jackson的ObjectNode作为contains方法参数时，jOOQ无法自动识别该参数为JSONB类型，不会生成PostgreSQL原生JSONB的@>（结构化包含）运算符逻辑，反而会退化为字符串级别的子串匹配，一旦JSON字符串的字段顺序、空格格式和数据库存储的不一致，就会出现明明有数据却查不到的问题。

首先将需要匹配的JSON结构转换为jOOQ内置的JSONB类型，再传入contains方法，jOOQ就会自动生成符合预期的@>运算符查询：

import org.jooq.JSONB
import com.fasterxml.jackson.databind.ObjectMapper

// 先将待匹配的JSON节点序列化为JSON字符串，包装为jOOQ的JSONB类型
val matchCondition = JSONB.valueOf(
    ObjectMapper().writeValueAsString(
        JsonNodeFactory.instance.objectNode().put("transactionId", transactionId)
    )
)

// 执行查询
jooq.selectFrom(Tables.TRANSACTIONS_TABLE)
    .where(TRANSACTIONS_TABLE.MESSAGE.contains(matchCondition))
    .fetch { TransactionTable.fromRecord(it) }

如果你使用的jOOQ版本较旧，无法自动将JSONB类型的contains映射为@>运算符，可以直接用原生SQL语法显式指定运算符，避免类型推断错误：

import org.jooq.impl.DSL

jooq.selectFrom(Tables.TRANSACTIONS_TABLE)
    .where(
        DSL.condition("{0} @> {1}", 
            TRANSACTIONS_TABLE.MESSAGE,
            DSL.val(matchCondition)
        )
    )
    .fetch { TransactionTable.fromRecord(it) }

• 不要直接将Jackson的JsonNode、普通POJO作为参数传给JSONB字段的contains方法，这类参数jOOQ默认会按普通对象处理，不会序列化为合法的JSONB参数
• 确认jOOQ代码生成阶段识别到MESSAGE列是JSONB类型，如果生成的字段类型是字符串或者普通JSON类型，将无法使用结构化包含查询，需要调整代码生成器的类型映射配置
• 校验传入的transactionId类型和数据库中存储的类型一致，例如数据库中存的是字符串格式ID，传入数字类型时会因为JSON类型不匹配导致查询无结果

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