How can I use JOOQ record's update to update only the embedded changes in a jsonb field?

Question

I have already binding from JSONB to my POJO (https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/)

And, I already saw JOOQ's #5394 issue (https://github.com/jOOQ/jOOQ/issues/5394) and the workaround temp solution.

Now I am trying to understand how can I detect changes inside my POJO and make JOOQ update only these changes with jsonb_set

Answer 1

The issue #5394 you've linked is not strictly related to your request. #5394 is about ordinary, normalised UpdatableRecord instances, where some values should not be automatically considered for insertion or updates.

In your case, you're working with nested data structures in the form of JSON. To jOOQ, a JSON column is opaque and does not have any notion of nested structure. If you want to introduce this functionality, you'll have to do it entirely yourself. You cannot, however, do this currently (jOOQ 3.13) with UpdatableRecord and record.store() calls. You will have to write your own API that translates such record changes to insert or update statements.

I can think of two ways to do this:

• Using client side JSON comparison mechanisms (you might find something Java / POJO based, too, but I think it would be easier on the JSON level)
• Using server side triggers, which replace your update of the complete JSON data structure by a delta update.