CODEWITHSUNDEEP
javapostgresqlhibernatejpaspring-data-jpa
Ima Miri
Ima Miri

Reputation: 967

JPA - how to find a string list in a postgres text array

I have a text array and crud operations are working well. Now, I would like to find a list of string using a native query. I've tried the following solution

@Query(nativeQuery = true, value= "select * from biz_db.biz_user where :skills=ANY(skills) and " +
        "expert=1 and enabled=1 and " +
        "verified=1 order by creation_date desc limit 3")
List<User> findAllExpertsBySkills(@Param("skills") String[] skills);

Which I get 

Caused by: org.hibernate.QueryException: Named parameter not bound : skills

Any help appreciated.

here is the skills in User class

@Type( type = "string-array" )
@Column(
        name = "skills",
        columnDefinition = "text[]"
)
private String[] skills;

And in the table is defined

skills text[] NULL,

Upvotes: 2

Views: 5000

Answers (3)

Chen
Chen

Reputation: 77

where array_position(skills, :skills) > 0

it could be also your db mapping to entity problem,

test with @Transit for entity property

Upvotes: 0

coladict
coladict

Reputation: 5095

Well firstly there's the use of a wrong operator. I believe what you're looking for is overlap between the two? The operator would be &&, making this the where clause:

WHERE skills && :skills

If you want the table field to contain all the elements from the parameter, then use the @> operator, as shown in the docs

WHERE skills @> :skills

Now all that remains is the data type. For strings, numbers and dates, you can just add my library to your project and it will work directly. There are types for which it won't work, like UUID, but I haven't done extensive enough testing for others.

Upvotes: 1

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59950

The syntax of Any should be :

WHERE skills = ANY(:skills)

For more details take a look at 9.20.4. ANY/SOME

expression operator ANY (array expression)

Upvotes: 2

Related Questions