在具有jooq的UPDATE中使用原始值表达式

问题描述

这是我要执行的查询:

UPDATE TABLE users SET metadata = metadata - 'keyA' - 'keyB'
    WHERE <condition>;

此处，metadata的类型为jsonb，并且-运算符从JSON对象中删除密钥.但是，当我在jooq中执行此操作时:

Here, metadata is of type jsonb and the - operator removes a key from the JSON object. However, when I do this in jooq:

this.ctx.update(Tables.USERS)
    .set(Tables.USERS.METADATA, "metadata-'keyA'-'keyB'")
    .where(<condition>)
    .execute();

我收到一条错误消息，说value是CHARACTER VARYING而不是JSONB，我猜这是因为使用绑定值创建了查询，然后试图插入整个字符串而不是表达.

I get an error saying that the value is a CHARACTER VARYING and not JSONB, which I am guessing is because the query is being created with a bind value, and then entire string is being trying to be inserted rather than as an expression.

如何在jooq中执行此值表达式?

How do I execute this value-expression in jooq?

推荐答案

您要传递给set方法的内容:

What you're passing to the set method:

"metadata-'keyA'-'keyB'"

...不是直接插入到结果SQL字符串中的表达式.这是类型为String(即VARCHAR)的绑定变量.最简单的方法是使用普通SQL":

... 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")
))

有关在jOOQ中使用普通SQL"的更多信息，请参阅本手册的以下部分: http://www.jooq.org/doc/latest/manual /sql-building/plain-sql

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

这篇关于在具有jooq的UPDATE中使用原始值表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！