jOOQ使用multiset查询JSONB字段报org.jooq.JSONB实例化错误及排序问题

异常原因及解决方案 #

你遇到的异常和PostgreSQL下jOOQ 3.15版本的multiset仿真逻辑直接相关，和查询语句本身无关。
jOOQ 3.15版本对不支持原生multiset语法的数据库（包括当时的PostgreSQL版本）是基于JSON序列化/反序列化实现multiset仿真的：在执行查询时会把multiset的结果序列化成JSON字符串返回，jOOQ拿到结果后再用Jackson反序列化为对应的集合对象。
当multiset中包含JSONB类型字段时，序列化阶段会直接把JSONB的对象内容嵌套到multiset的整体JSON结构中，反序列化时Jackson尝试将嵌套的JSON对象转为org.jooq.JSONB类型，但默认没有配置对应类型的反序列化器，就会抛出你遇到的MismatchedInputException。当查询结果没有关联storage_coordinate_instance的产品时，hierarchy字段全为null，不会触发反序列化逻辑，所以运行正常。

对应解决方案有三种可根据实际情况选择：

• 方案1：升级jOOQ到3.16及以上版本。官方在后续版本修复了嵌套JSON类型的multiset反序列化问题，同时PostgreSQL 14+原生支持multiset语法，新版本会优先用原生实现避免序列化问题。
• 方案2：如果不能升级版本，可以修改POJO的字段类型：将ProductStorageItem中的hierarchy字段类型从JSONB改为String，需要使用时再通过JSONB.valueOf(hierarchyStr)手动转为JSONB类型。
• 方案3：给jOOQ的Jackson配置注册jOOQ Jackson扩展模块，引入org.jooq:jooq-jackson-extensions依赖后，在初始化dslContext时配置Jackson的ObjectMapper注册JooqModule，即可支持JSON/JSONB类型的反序列化。

multiset结果排序实现 #

按存储位数量排序 #

推荐直接在查询中新增存储位数量的计算字段，数据库层面直接排序效率更高，示例代码如下：

List<ProductFilterItem> items =
    dslContext
        .select(
            PRODUCT.ID,
            PRODUCT.NAME,
            PRODUCT.ARTICLE_NUMBER,
            PRODUCT.PHYSICAL,
            // 新增存储位计数字段用于排序
            DSL.field(DSL.selectCount()
                .from(PRODUCT_STORAGE)
                .where(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))
            ).as("storage_count"),
            multiset(
                select(
                    PRODUCT_STORAGE.ID,
                    PRODUCT_STORAGE.PRODUCT_ID,
                    PRODUCT_STORAGE.STOCK,
                    PRODUCT_STORAGE.COORDINATE_INSTANCE_ID,
                    STORAGE_COORDINATE_INSTANCE.HIERARCHY
                )
                    .from(PRODUCT_STORAGE)
                    .leftOuterJoin(STORAGE_COORDINATE_INSTANCE).on(STORAGE_COORDINATE_INSTANCE.ID.eq(PRODUCT_STORAGE.COORDINATE_INSTANCE_ID))
                    .where(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))
            ).as("storage").convertFrom(r -> r.into(ProductStorageItem.class))
        )
        .from(PRODUCT)
        .where(queryCondition)
        // 按存储位数量倒序，数量多的排在前面
        .orderBy(DSL.field("storage_count").desc())
        .fetchInto(ProductItem.class);

有存储位的产品优先排序 #

仅需修改排序逻辑即可，示例如下：

.orderBy(
    // 有存储位的产品返回1，无存储位的返回2，升序排列即可让有存储位的在前
    DSL.when(DSL.field("storage_count").gt(0), 1).otherwise(2).asc(),
    // 可追加其他排序规则，比如按产品ID排序
    PRODUCT.ID.asc()
)

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