[SOLVED] JOOQ update JSONB column with concat operator

JOOQ update JSONB column with concat operator

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.

Solution

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) {
    return DSL.field("({0} || {1})", f1.getDataType(), f1, f2);
}