Using Postgres JSONB query with Spring Data and bind parameter fails with InvalidDataAccessApiUsageException

Question

I am currently looking for a solution for the exception

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that position [1] did not exist;

My current @Query annotation is:

@Query(
    nativeQuery = true, 
    value = "SELECT * FROM thgcop_order_placement WHERE \"order_info\" @> '{\"parentOrderNumber\":\" :param \"}'")

I guess the position [1] did not exist comes from it being in double quotes plus double quote plus single quote.

How can I make this work?

The query is using Postgres JSONB datatype. The column definition is ORDER_INFO JSONB

The following native query works just fine in the Postgres client:

SELECT * FROM thgcop_order_placement
WHERE "order_info" @> '{"parentOrderNumber":"ORD123"}'

Answer 1

None of the above worked for me except the below,

Service Layer code :-

OrderInfo orderInfo = new OrderInfo();
orderInfo.setParentOrderNumber("ORD123");
....
String param = objectMapper.writeValueAsString(orderInfo);
List<Order> list = jpaRepository.getByParentOrderNumber(param);

JpaRepository.java code :-

@Query(nativeQuery = true, value = "select * from thgcop_order_placement where order_info @> CAST(:condition as jsonb)")
List<Order> getByParentOrderNumber(@Param("condition") String parentOrderNumber);

This is how I achieve the result. I hope this will be very helpful for all enthusiastic!!

Thank you all for your help !!!

Answer 2

TL;DR: Make it work with Bind Parameter and plain JDBC first. Then move on to Spring Data, possibly falling back on a custom implementation.

You are facing problems on many levels here.

1. Let's start with ignoring Spring Data for now. The statement you showed is very dissimilar from the one you try to construct with Spring Data because it doesn't contain a bind variable. So instead of

   SELECT * FROM thgcop_order_placement WHERE "order_info" @> '{"parentOrderNumber":"ORD123"}'
   

   We should compare it to

   SELECT * FROM thgcop_order_placement WHERE "order_info" @> '{"parentOrderNumber": ? }'
   

   Note that we are losing the quotes since they denote a literal String but we aren't providing a literal String but a bind parameter.

2. I haven't found any indication that you can use bind parameters in parts of JSON expressions. So instead of the statement above we would need to use:

   SELECT * FROM thgcop_order_placement WHERE "order_info" @> ?
   

   Of course, the bind parameter should then contain the complete JSON expression

3. Unfortunately, this doesn't seem to work either because now Postgres considers the bind parameter a VARCHAR instead of a JSON expression. See https://blog.2ndquadrant.com/processing-json/. I think the correct version should be

   SELECT * FROM thgcop_order_placement WHERE "order_info" @> ?::json
   

   But I couldn't get this to work either.

4. In any case, you are left to transform your parameter to the JSON structure. Normally I'd suggest using a SpEL expression for this. But it won't work because Spring Data chokes on the curly braces needed in the SpEL expression and considers them the end of the SpEL expression.

5. If you get something like this to work with a simple JDBC connection or JdbcTemplate you can start to think about @Query annotations.

   @Query(
       value= "SELECT * FROM thgcop_order_placement WHERE \"order_info @> :name::json",
       nativeQuery = true)
   

This might trigger more problems since Spring Data will either consider ::json part of the parameter name. If this is the case you'll have to fall back on custom implementations.

I ran a couple of experiments, which you can look at and play with here.