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.

Answer 1

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!