Spring boot Spring Data JPA postgres jsonb column INSERT/UPDATE

Question

i'm struggling trying to update a record on a postgres table with a jsonb column.

What i'm trying to do with spring boot and JPA is running this statement that works great on postgres:

UPDATE data_report 
SET error_details  = error_details || '[{"error": "testError","record": {"start":"14:00","end":"20:00","id":"AAAA001","date":"2022-01-31"}}]'::jsonb
WHERE id = 91;

I've tried with Native Query:

   @Transactional
   @Modifying
   @Query(value = "UPDATE data_integration_report SET error_details = error_details || :errors ::jsonb WHERE id = :id", nativeQuery = true)
    void updateErrorDetail(@Param("id") Long id, @Param("errors") String errors);

but I'm getting error saying that syntax is not correct because ::jsonb is not recognized

I've tried with EntityManager

entityManager.createNativeQuery(
       "UPDATE data_integration_report SET error_details = error_details || :errors ::jsonb WHERE id = :id"
        ).setParameter("id", 91L)
        .setParameter("errors", data)
        .executeUpdate();

Even here i'm getting error on syntax. I've also tried to remove ::jsonb cast, but I'm receiving this error: "column is of type jsonb but expression is of type text"

I'm looking for some documentation or an example that can help me to find a solution. Thanks in advance.

Does this answer your question? how to store PostgreSQL jsonb using SpringBoot + JPA? — jamesngyz
– jamesngyz, Commented Jan 31, 2022 at 22:38

egeorge · Accepted Answer · 2022-10-21 14:15:34Z

I think the immediate problem you are having is casting the incoming field incorrectly.

@Transactional
@Modifying
@Query(value = "UPDATE data_integration_report SET error_details = error_details || :errors ::jsonb WHERE id = :id", nativeQuery = true)
void updateErrorDetail(@Param("id") Long id, @Param("errors") String errors);

:errors ::jsonb has a space, so it sees the :: cast operation as a separate token. However, JPA will choke on :errors::jsonb (as I suspect you have discovered).

There are two ways to do a Postgres cast inside a query like this:

Escape the colons: :error\\:\\:jsonb
Use the cast function: cast(:error as jsonb)

However

There is an even better solution, and that is to use a hibernate type made for jsonb in your entity. The commonly accepted solution is this one: vladmihalcea / hibernate-types

Collectives™ on Stack Overflow

Spring boot Spring Data JPA postgres jsonb column INSERT/UPDATE

1 Answer 1

However

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

However

Comments

Linked

Related