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:

Unnest the document column
Filter out the document that should be updated of the array
Append the document that should be updated
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.

Is normalising this to some extent possible? I.e. have a create table documents (document_id uuid, issue_id bigint, document jsonb) table? It seems quite easier to manage your data this way... — Lukas Eder
– Lukas Eder, Commented Nov 24, 2020 at 11:32
Unfortunately not - without too big impact on the rest of the application and project schedule, but I agree with you... — Andreas
– Andreas, Commented Nov 24, 2020 at 13:08

Lukas Eder · Accepted Answer · 2020-11-24 15:05:10Z

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?

answered Nov 24, 2020 at 15:05

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres/jOOQ replace jsonb[] element

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related