How to update a JSONB column in PostgreSQL using a Spring Data JPA Query

Question

I have the following Controller

@RequestMapping(value = "/update/{tableId}", method = RequestMethod.PUT, produces = MediaType.APPLICATION_JSON_VALUE,
            consumes = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity updateItemQty (@PathVariable Long tableId, @RequestBody AddItemsRequestBody requestBody,
                                              HttpServletRequest request){
        try {
            String addedBy = getUserName(request);
            Float ItemQuantity = requestBody.getItemQuantity();
            LocalDateTime dateTimeAdded = LocalDateTime.now();
            String subId = requestBody.getItemSubId();
            ArrayList<ItemAdditionDetails> updatedAdditionDetails = new ArrayList<>();

        ItemAdditionDetails newItemAdditionDetails = new ItemAdditionDetails();
        newItemAdditionDetails.setIncreamentCount(ItemQuantity);
        newItemAdditionDetails.setAddedDateTime(dateTimeAdded);
        newItemAdditionDetails.setAddedBy(addedBy);

        updatedAdditionDetails.add(newItemAdditionDetails);

// Here i am fetching a JSON-B column that returns an array of json objects from Postgress DB  
     ItemInventoryService.findByItemSubId(subId).getItemsInventoryAdditionDetails().forEach((el) -> {
                updatedAdditionDetails.add(el);
            });

        itemInventoryService.updateItemsAdditionDetails(subId,updatedAdditionDetails);

        return new ResponseEntity("Updated", HttpStatus.CREATED);
    }catch (Exception ex){
        return new ResponseEntity(ex, HttpStatus.INTERNAL_SERVER_ERROR);
    }

}

If i remove/comment

ItemInventoryService.findByItemSubId(subId).getItemsInventoryAdditionDetails().forEach((el) -> {
                updatedAdditionDetails.add(el);
            });

That is i don't add the fetched result to the new updatedAdditionDetails (of type ArrayList), the DB updates successfully Otherwise i get the below error :

ERROR: column \"itemsinventory_addtion_details\" is of type jsonb but expression is of type record\n Hint: You will need to rewrite or cast the expression.\n Position

In my Repository i have the following query

@Transactional
    @Modifying
    @Query(value = "UPDATE items_inventory  SET itemsinventory_addtion_details = :updatedAdditionDetails WHERE item_subid = :subId", nativeQuery = true)
    Integer updateItemsAdditionDetails(@Param("subId") String subId, @Param("updatedAdditionDetails") List<ItemAdditionDetails> updatedAdditionDetails);

I have tried it the JPA way as follows:

 @Query(value = "UPDATE ItemsInventory items  SET items.itemsInventoryAdditionDetails = :updatedAdditionDetails WHERE items.itemSubId = :subId")

This unfortunately has fetched the same error. Any help on this is greatly appreciated. Thanks in advance.

Can you add your jpa bean definition? Here maybe this will help stackoverflow.com/questions/49325802/… — Pawel Zieminski
– Pawel Zieminski, Commented Jun 8, 2020 at 7:22
Sorry, just noticed you don't use an entity bean in this. Still, see the link above. — Pawel Zieminski
– Pawel Zieminski, Commented Jun 8, 2020 at 7:59

Vlad Mihalcea · Accepted Answer · 2021-01-08 16:02:55Z

6

The cause of the error

The PostgreSQL error looks as follows:

ERROR: column "itemsinventory_addtion_details" is of type jsonb but expression is of type record\n Hint: You will need to rewrite or cast the expression.\n Position

This error is caused because the List is passed like this:

UPDATE
    ItemsInventory items
SET
    items.itemsInventoryAdditionDetails = (?, ?, ?, .., ?)
WHERE
    items.itemSubId = ?

By default, Spring Data JPA, which uses Hibernate, doesn't know how to handle JSON types. So, you need to use a custom Hibernate Type, like the JsonBinaryType offered by the Hibernate Types project.

Maven dependency

First, you need to add the Hibernate Types dependency:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

After you set the hibernate-types.version Maven property to the latest version, the dependency will be downloaded from Maven Central.

Setting the Hibernate Type explicitly

You won't be able to use the Spring @Query annotation since you can't set the Hibernate Type explicitly.

So, you need to add a custom Spring Data JPA Repository implementation with the following method:

public int updateItemsAdditionDetails(
    String subId, 
    List<ItemAdditionDetails> updatedAdditionDetails) {
    return entityManager.createNativeQuery(
        "UPDATE items_inventory  SET itemsinventory_addtion_details = :updatedAdditionDetails WHERE item_subid = :subId")
    .unwrap(NativeQuery.class)
    .setParameter("updatedAdditionDetails", updatedAdditionDetails, JsonBinaryType.INSTANCE)
    .setParameter("subId", subId)
    .executeUpdate();
}

That's it!

edited Jan 8, 2021 at 16:02

answered Jun 9, 2020 at 4:35

Vlad Mihalcea

156k85 gold badges598 silver badges984 bronze badges

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

2 Comments

ShibenDutta Over a year ago

Thanks for the input. The query returns null for the above implementation. In my ItemInventoryRepositoryCustom int updateItemsAdditionDetails(String subId, List<ItemAdditionDetails> updatedAdditionDetails); In my ItemInventoryRepositoryImpl i have implemented the above suggested method. Finally my JPA repo public interface ItemInventoryRepository extends JpaRepository<ItemsInventory, Long>, ItemInventoryRepositoryCustom{} The query returns "message": null, "suppressed": [], "localizedMessage": null What am i doing wrong here.

Vlad Mihalcea Over a year ago

You're welcome. I'm sure you are going to find out the solution now. It's just a matter of debugging the problem.

Klesun · Accepted Answer · 2022-08-26 14:30:58Z

Another option I found is to just serialize your collection into string before feeding it to the query:

@Transactional
@Modifying
@Query(value = "UPDATE items_inventory  SET itemsinventory_addtion_details = :updatedAdditionDetails WHERE item_subid = :subId", nativeQuery = true)
Integer updateItemsAdditionDetails(
    @Param("subId") String subId,
    @Param("updatedAdditionDetails") String updatedAdditionDetails
                                  /* ^^^^^^ here */
);

default Integer updateItemsAdditionDetails(
    @Param("subId") String subId,
    @Param("updatedAdditionDetails") List<ItemAdditionDetails> updatedAdditionDetails
) {
    String updatedAdditionDetailsStr = new com.fasterxml.jackson.databind
        .ObjectMapper().writeValueAsString(updatedAdditionDetails);

    return updateItemsAdditionDetails(subId, updatedAdditionDetailsStr);
}

Postgres seems to implicitly cast it to jsonb without problem.

Collectives™ on Stack Overflow

How to update a JSONB column in PostgreSQL using a Spring Data JPA Query

2 Answers 2

The cause of the error

Maven dependency

Setting the Hibernate Type explicitly

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

The cause of the error

Maven dependency

Setting the Hibernate Type explicitly

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related