Using MyBatis mapper.xml to increment a multiple counters in the same query inside of a JSONB field in Postgresql

Question

I am working with Java + Spring + MyBatis + Postgresql. I have a table with a JSONB column in which there are a series of BIGINT (which are mapped to Long in Java), that for performance reason I want to update using a query inside of the mapper.xml relative to the entity the attribute belongs to.

Supposing that I the following elements:

• table_a: a database table
• column_1: column of table_a of type JSONB
• key_alpha: field of column_1 associated to a BIGINT value

Ideally in pure psql syntax I would do the following to initialize such value:

UPDATE table_a
SET column_1 = jsonb_set(column_1, '{key_alpha}', to_jsonb(0));

And the following to increment it:

UPDATE table_a
SET column_1 = jsonb_set(column_1, '{key_alpha}', to_jsonb((column_1->>'key_alpha')::bigint + 1));

The problem is that it looks like that this syntax is not compatible with MyBatis xml mapper syntax. How can I achieve this result using MyBatis, or by using an alternative method, while keeping in mind that I need a performance-critical approach?

More specifically, if I try to use this syntax in the mapper.xml:

<if test="incrementCountAlpha != null">column_1 = jsonb_set(column_1, '{key_alpha}', to_jsonb((column_1->>'key_alpha')::bigint + #{incrementCount})),</if>

<if test="incrementCountBeta != null">column_1 = jsonb_set(column_1, '{key_beta}', to_jsonb((column_1->>'key_beta')::bigint + #{incrementCount})),</if>

I get the following error; ### Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: multiple assignments to same column "column_1"

Answer 1

The following statement should achieve your goal.

<update id="updateColumn1">
  update table_a set column_1 = column_1
  <if test="incrementCountAlpha != null">
    || jsonb_build_object(
      'key_alpha',
      coalesce((column_1->>'key_alpha')::bigint, 0) + #{incrementCountAlpha})
  </if>
  <if test="incrementCountBeta != null">
    || jsonb_build_object(
      'key_beta',
      coalesce((column_1->>'key_beta')::bigint, 0) + #{incrementCountBeta})
  </if>
</update>

COALESCE might be unnecessary if the target field is guaranteed to be initialized beforehand.

Here is an executable demo project:
https://github.com/harawata/mybatis-issues/tree/master/so-77490757