JPA and JSON operator native update query

Question

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);

Ancoron · Accepted Answer · 2019-05-01 22:14:24Z

1

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)

edited May 1, 2019 at 22:14

answered May 1, 2019 at 15:00

Ancoron

2,7811 gold badge14 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hm1 Over a year ago

above mentioned code didn't work. :newId and = :oldId is still issue anyways

Ancoron Over a year ago

Can you add to your question which error you are getting? Just fixed the type conversion for function jsonb_set, but parameter oldId should already be a character varying or text, which are comparable.

Collectives™ on Stack Overflow

JPA and JSON operator native update query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related