CODEWITHSUNDEEP
javapostgresqlspring-bootjsonb
Davide System
Davide System

Reputation: 61

Spring boot Spring Data JPA postgres jsonb column INSERT/UPDATE

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.

Upvotes: 2

Views: 4001

Answers (1)

egeorge
egeorge

Reputation: 682

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:

  1. Escape the colons: :error\\:\\:jsonb
  2. 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

Upvotes: 4

Related Questions