英文:
JOOQ update JSONB column with concat operator
问题
我有一个表格，其中一个列的数据类型是JSONB，并且我试图在PostgreSQL中使用 JSONB concat (||) 运算符来更新jsonb列的内容。以下是我想要转换为jOOQ的示例纯SQL查询（插入/更新示例）。
create table foobar(id text primary key, address jsonb not null);insert into foobar(id, address)values ('1234', '["a", "b"]'::jsonb);-- insert / update exampleinsert into foobar(id, address)values ('1234', '["a", "b"]'::jsonb)on conflict (id) DO UPDATEset address = foobar.address || '["C"]'::jsonbwhere foobar.id = '1234';-- explicit update exampleupdate foobarset address = address || '["D"]'::jsonbwhere id = '1234';
select * from foobar;以上查询的结果：1234,"["a", "b", "C", "D"]"
我看到这个问题中支持concatenation仍然未解决，但想检查是否有任何可以使用的解决方法。
英文:
I have a table with one of the column data type as JSONB and I'm trying to use the JSONB concat (||) operator in postgresql to update the content for the jsonb column. Below is the sample plain sql query (insert / update example) which I want to convert to jooq.
create table foobar(id text primary key, address jsonb not null);insert into foobar(id, address)values ('1234', '["a", "b"]'::jsonb);-- insert / update exampleinsert into foobar(id, address)values ('1234', '["a", "b"]'::jsonb)on conflict (id) DO UPDATEset address = foobar.address || '["C"]'::jsonbwhere foobar.id = '1234';-- explicit update exampleupdate foobarset address = address || '["D"]'::jsonbwhere id = '1234';
select * from foobar;Result for above query:1234,"["a", "b", "C", "D"]"
I did see this issue for supporting concatenation is still open but want to check if there is any work around I can use.
答案1
得分: 0
在 jOOQ 中，如果您缺少对某些特定供应商功能的支持，始终可以使用 纯 SQL 模板化 解决方法：
Field<JSONB> jsonbConcat(Field<JSONB> f1, Field<JSONB> f2) {DSL.field("({1} || {2})", f1.getDataType(), f1, f2);}
英文:
There's always the plain SQL templating workaround in jOOQ, if you're missing support for some vendor specific functionality:
Field<JSONB> jsonbConcat(Field<JSONB> f1, Field<JSONB> f2) {DSL.field("({1} || {2})", f1.getDataType(), f1, f2);}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论