Postgres/jOOQ replace jsonb[] element

Question

I'm having a Spring application with jOOQ and Postgresql database having a table (issues) with the following two columns:

id (Long)
documents (jsonb[]) <- array of jsonb (not jsonb array)

The document json structure is on the following format:

{
   "id": (UUID),
   "name": (String),
   "owner"; (String)
}

What I want to achieve is to be able to replace documents with matching id (normally only one) with a new document. I'm struggling with the jOOQ or even the plain SQL.

I guess I need to write some plain SQL in jOOQ to be able to do this but that is ok (to a minimum). I had an idea to do the following:

1. Unnest the document column
2. Filter out the document that should be updated of the array
3. Append the document that should be updated
4. Store the whole array

Raw SQL looks like this but missing the new document to be added:

UPDATE issues SET documents = (SELECT ARRAY_AGG(doc) FROM issues, UNNEST(issues.documents) AS doc WHERE doc->>'id' != 'e4e3422f-83a4-493b-8bf9-37980d532538') WHERE issues.id = 1;

My final goal is to write this in jOOQ and append the document to be replaced. I'm using jOOQ 3.11.4.

Answer 1

You should be able to just concatenate arrays in PostgreSQL:

UPDATE issues 
SET documents = (
  SELECT ARRAY_AGG(doc) || '{"id":"e4e3422f-83a4-493b-8bf9-37980d532538","name":"n"}'::jsonb
  FROM issues, UNNEST(issues.documents) AS doc 
  WHERE doc->>'id' != 'e4e3422f-83a4-493b-8bf9-37980d532538'
)
WHERE issues.id = 1

Some common array functions will be added to jOOQ in the near future, e.g. array concatenation, but you can get away for now with plain SQL templating I suspect?