We have a legacy database column of types jsonb which can store both array and json . Eg.
items
-------
["elem1","elem2"]
["elem3","elem4"]
{"elem31": ["src1", "src2", "src3"], "elem100": ["src5"]}
{"elem21": ["src1", "src2"]}
Now to search for specific row which contains any element I am using this query:
select * from tbl1 where item ? 'elem21' // if searching for single element
select * from tbl1 where item ?| '{elem21,elem1}' // if any rows matches any of item in list element.
Above queries run fine in psql console.
How to run the above query in Spring boot native query ?
Writing the Query as
@Query("select * from tbl1 where item ? :elem ", nativeQuery=true)
List<Object[]> findBySingleElement(@Param("elem") String element)
@Query("select * from tbl1 where item ?| (:elems)", nativeQuery=true)
List<Object[]> findByAnyElement(@Param("elems") String[] elements)
// -------positional example----
@Query("select * from tbl1 where item ? ?1", nativeQuery=true)
List<Object[]> findBySingleElement(String element)
@Query("select * from tbl1 where item ?| (?1)", nativeQuery=true)
List<Object[]> findByAnyElement(String[] elements)
This does not compile giving error as
We should not match ? and parameterized annotations.
java.lang.IllegalArgumentException: Mixing of ? parameters and other forms like ?1 is not supported
Using ?1 instead of :elem is giving missing parameter exception.
How to escape ? when ? is in comparator operator (I also tried ??| and \\?| but these does not work.) Or is there any way to write these kind of queries which I am missing.
PS. As of now I have created psql custom function through db migrations and use it directly.