Question 200146 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Spring JPA Query – JSON_REMOVE – Pass query index dynamically

I’m using JSON_REMOVE method for removing one field from my JSON array column. Below is the query I wrote in JPA –

@Modifying
@Transactional
@Query("UPDATE Data d SET d.month = JSON_REMOVE(d.month, '$[0].revenue') "
        + " WHERE d.year = :year ")
void resetRevenue(Integer year);

The above query works. But now I want to dynamically pass the index value like –

@Modifying
@Transactional
@Query("UPDATE Data d SET d.month = JSON_REMOVE(d.month, '$[ :index ].revenue') "
        + " WHERE d.year = :year ")
void resetRevenue(Integer index, Integer year);

When I try above I get error – java.sql.SQLSyntaxErrorException: Invalid JSON path expression. The error is around character position 3.

I even tried below query –

@Modifying
@Transactional
@Query("UPDATE Data d SET d.month = JSON_REMOVE(d.month, '$[ ?1 ].revenue') "
        + " WHERE d.year = ?2 ")
void resetRevenue(Integer index, Integer year);

But got error – org.springframework.dao.InvalidDataAccessResourceUsageException: Ordinal parameter not bound : 2

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The solution as commented by Kedar Thakkar worked. Using concat to append my dynamic index was successful and gave me appropriate result.

    @Query("UPDATE Data d SET d.month = JSON_REMOVE(d.month, CONCAT('$[', :index, '].revenue')) "+ " WHERE d.year = :year ")

  2. 0

    The issue you’re facing is that JPA doesn’t directly support parameter binding for JSON path expressions. To work around this, you can construct the query dynamically using native SQL syntax. Here’s an example:

    @Modifying
@Query(value = "UPDATE Data d SET d.month = JSON_REMOVE(d.month, CONCAT('$[', :index, '].revenue')) WHERE d.year = :year", nativeQuery = true)
void resetRevenue(@Param("index") Integer index, @Param("year") Integer year);

    In this approach, you use the CONCAT function to dynamically construct the JSON path expression. Make sure to include the @Param annotation to map the method parameters to the named parameters in the query.

    By following this method, you should be able to dynamically pass the index value and remove the specified field from the JSON array column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search