Using Raw Value-expressions In Update With Jooq

This is the query I am trying to execute: UPDATE TABLE users SET metadata = metadata - 'keyA' - 'keyB' WHERE ; Here, metadata is of type jsonb and the - opera

Solution 1:

What you're passing to the set method:

"metadata-'keyA'-'keyB'"

... is not an expression that is directly injected into the resulting SQL string. It's a bind variable of type String (i.e. VARCHAR). The easiest way forward would be to resort to using "plain SQL":

.set(USERS.METADATA, field(
     "{0} - {1} - {2}", 
     USERS.METADATA.getDataType(),
     USERS.METADATA, val("keyA"), val("keyB")
))

For more information related to using "plain SQL" with jOOQ, please refer to this section of the manual: http://www.jooq.org/doc/latest/manual/sql-building/plain-sql