CODEWITHSUNDEEP
postgresqlkotlinjooq
λ Jonas Gorauskas
λ Jonas Gorauskas

Reputation: 6198

Using jsonb_set in update with jOOQ

I have a sql query for updating a status value in a data column of type jsonb in Postgresql that looks like this:

update sample
set updated = now(),
    data = jsonb_set(data, '{status}', jsonb 'CANCELLED', true)
where id = 11;

I need to translate that to a working jOOQ query in my Kotlin project ... I have this so far:

jooq.update(Tables.SAMPLE)
        .set(Tables.SAMPLE.UPDATED, OffsetDateTime.now())
        .set(Tables.SAMPLE.DATA, field("jsonb_set(data, '{status}', jsonb '\"CANCELLED\"', true)"))
        .where(Tables.SAMPLE.ID.eq(id))
        .execute()

But the second set fails with None of the following functions can be called with the arguments supplied error message... What is the correct signature of set that I can use here?

I am basing my jOOQ syntax on the answer that Lukas Eder provided in Using raw value-expressions in UPDATE with jooq

Upvotes: 2

Views: 2130

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

In an UPDATE statement, you have to match data types in your SET clause on both sides. I.e. SAMPLE.DATA is of type Field<T>, so the expression you're setting it to must also be of type Field<T>.

I'm assuming that SAMPLE.DATA is a Field<JSONB>, so it will be sufficient to write

.set(SAMPLE.DATA, field("json_set(...)", JSONB.class))

Notice that jOOQ 3.12 has introduced this JSONB type. In previous versions, lacking any out-of-the-box jOOQ representation for JSON and JSONB types, the jOOQ code generator may have generated a Field<Object> type for your SAMPLE.DATA column, in case of which your statement would have compiled.

Upvotes: 1

Related Questions