如何用jOOQ以字符串替换方式更新PostgreSQL的JSONB[]字段

问题分析 #

你当前代码的核心问题是类型不匹配：MY_TABLE.ARRAY_FIELD对应Array<JSONB?>类型，但你直接传入了字符串jsonArrayAsString，jOOQ无法自动完成这种类型转换，导致语法错误。另外，contentDeepToString()生成的是Java数组的字符串表示，和PostgreSQL要求的JSONB[]输入格式存在差异，直接替换容易引发JSON格式异常。

正确实现方案 #

方案1：数据库层面处理（推荐） #

利用PostgreSQL的数组、JSON原生函数直接在SQL中完成替换，避免客户端与数据库的类型转换问题，同时保证操作的原子性：

import org.jooq.impl.DSL.*

// 替换所有JSON对象中值的"val"子串
val updateCount = conn.update(MY_TABLE)
    .set(
        MY_TABLE.ARRAY_FIELD,
        field(
            """
            array(
                select jsonb_object_agg(k, replace(v, 'val', 'value')) 
                from unnest({0}) as e, jsonb_each_text(e) as j(k, v)
                group by e
            )
            """.trimIndent(),
            MY_TABLE.ARRAY_FIELD.dataType,
            MY_TABLE.ARRAY_FIELD
        )
    )
    .where(MY_TABLE.ID.eq(record.getId())) // 添加过滤条件锁定目标记录
    .execute()

如果仅需替换指定键（比如key1）的值，可简化为：

val updateCount = conn.update(MY_TABLE)
    .set(
        MY_TABLE.ARRAY_FIELD,
        field(
            "array(select jsonb_replace(e, '$.key1', replace(e->>'key1', 'val', 'value')) from unnest({0}) as e)",
            MY_TABLE.ARRAY_FIELD.dataType,
            MY_TABLE.ARRAY_FIELD
        )
    )
    .where(...)
    .execute()

方案2：客户端处理后正确转换类型 #

若必须在客户端处理，需将替换后的内容正确转换为数据库可识别的Array<JSONB?>类型：

import org.jooq.JSONB
import java.sql.Array

// 1. 获取原始JSONB数组
val originalArray = record.getValue(MY_TABLE.ARRAY_FIELD)

// 2. 遍历数组替换每个JSON对象的目标字符串
val replacedJsonbs = originalArray.map { jsonb ->
    jsonb?.let {
        val updatedJsonStr = it.data().replace("val", "value")
        JSONB.jsonb(updatedJsonStr)
    }
}

// 3. 转换为PostgreSQL兼容的SQL Array类型
val sqlArray = conn.createArrayOf("jsonb", replacedJsonbs.toTypedArray())

// 4. 执行更新
conn.update(MY_TABLE)
    .set(MY_TABLE.ARRAY_FIELD, sqlArray)
    .where(MY_TABLE.ID.eq(record.getId()))
    .execute()

关键注意点 #

• 禁止直接使用contentDeepToString()：该方法生成的是Java数组的字符串格式，与PostgreSQL要求的JSONB[]输入格式不兼容，会导致存储的JSON结构损坏。
• 优先选择数据库端处理：减少数据传输开销，利用数据库原生函数保证JSON格式的正确性和操作的原子性。

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