英文:
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 example
insert into foobar(id, address)
values ('1234', '["a", "b"]'::jsonb)
on conflict (id) DO UPDATE
set address = foobar.address || '["C"]'::jsonb
where foobar.id = '1234';
-- explicit update example
update foobar
set address = address || '["D"]'::jsonb
where 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 example
insert into foobar(id, address)
values ('1234', '["a", "b"]'::jsonb)
on conflict (id) DO UPDATE
set address = foobar.address || '["C"]'::jsonb
where foobar.id = '1234';
-- explicit update example
update foobar
set address = address || '["D"]'::jsonb
where 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);
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论