Question 295782 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Native query not replacing parameter and is there a JPQL version of this native query

I have a JSONB column order_information in Postgres table order_information_table with the below data:

{
    "response_list": [
        {
            "interest": 5,
            "number": "12221",
            "amount": 1200
        },
        {
            "interest": 1,
            "number": "12229",
            "amount": 800
        },
        {
            "interest": 2,
            "number": "12227",
            "amount": 2500
        }
    ]
}

I want to be able to get the database record by searching the JSONB column value’s number field.

When I used a native query with hardcoded number value then I see the result:

@Query("select * from order_information_table " +
  "where order_information -> 'response_list' @> '[{"number": "12221"}]'",
  nativeQuery = true)
fun retrieveOrdersForNumber(
  @Param("number") number: String
): List<OrderInformation>

But when I try to use parameter then it does not work:

@Query("select * from order_information_table " +
  "where order_information -> 'response_list' @> '[{"number": ":number"}]'",
  nativeQuery = true)
fun retrieveOrdersForNumber(
  @Param("number") number: String
): List<OrderInformation>

Why is the parameter :number not getting replaced with the value?

Is there a JPQL query possible for this instead of using a native query?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to Laurent Schoelens, this is the native SQL query:

    select * from order_information_table
where order_information -> 'response_list' @> jsonb_build_array(jsonb_build_object('number', '12221'))

    And this native SQL query can be executed in Spring Data as below:

    @Query("select * from order_information_table where " +
    "order_information -> 'response_list' @> jsonb_build_array(jsonb_build_object('number', cast(:number as VARCHAR)))",
    nativeQuery = true)
  fun retrieveOrdersForNumber(
    @Param("number") number: String
  ): List< OrderInformation >

  2. 0

    Since number is the first parameter, why don’t you change '[{"number": ":number"}]' to '[{"number": ?1}]'

    See here : https://www.baeldung.com/spring-data-jpa-query#2-native-2

    EDIT

    After more reading, I think the error is the json part beeing quoted.

    Since you’re using native queries, maybe you should try using JSON Creation Functions

    @Query("select * from order_information_table " +
  "where order_information -> 'response_list' @> jsonb_build_array(jsonb_build_object('number', :number))",
  nativeQuery = true)

    When running the following json function in psql, I get the following result :

    SELECT jsonb_build_array(jsonb_build_object('number', '12221'));
    jsonb_build_array    
------------------------
 [{"number" : "12221"}]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search