CODEWITHSUNDEEP
jsonpostgresqlspring-bootjpajsonb
hm1
hm1

Reputation: 186

JPA and JSON operator native update query

I'm trying to make this query work in JPA:

UPDATE billing_log_criteria
SET value = jsonb_set(value, '{cubPersonId}', '"12778269"')
where value ->> 'cubPersonId' = '12778268';

It works perfectly with postgresql but when I integrate it with JPA, I get the following error:

Caused by: org.postgresql.util.PSQLException: ERROR: function jsonb_set(jsonb, unknown, character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Following is the query i tried

@Modifying(clearAutomatically=false)
@Transactional
@Query(value = "UPDATE billing_log_criteria "
        + "SET value = jsonb_set(value, '{cubPersonId}',  jsonb_set(value, '{cubPersonId}', :newId)) "
        + "where value ->> 'cubPersonId' = :oldId", nativeQuery = true)
void updateCubPersonId(@Param("newId") String newId,@Param("oldId") String oldId);

calling like this

String oldId = "12778269";
updateCubPersonId( "'\" 12778268l \"'",oldId);

Upvotes: 0

Views: 1463

Answers (1)

Ancoron
Ancoron

Reputation: 2733

In psql, the argument '{cubPersonId}' is automagically converted to a text[]. In JDBC, this is not the case and you need to explicitly, e.g.:

@Query(value = "UPDATE billing_log_criteria "
        + "SET value = jsonb_set(value, '{cubPersonId}'::text[],  jsonb_set(value, '{cubPersonId}'::text[], to_jsonb(:newId::text))) "
        + "where value ->> 'cubPersonId' = :oldId", nativeQuery = true)

Upvotes: 1

Related Questions