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

Question

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,

I am not sure about the Hibernate message, how you can bound an array. But from Postgres view: :skills seems to be an array. So you are trying to do array = ANY(array). This is not supported. Maybe you could try the && operator: :skills && skills. This operator gives true, if both arrays overlap (share at least one element) — S-Man
– S-Man, Commented Aug 13, 2019 at 7:38
@S-Man I've tried that and got ''Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bytea && text[] Hint: No operator matches the given name and argument types. You might need to add explicit type casts.'' — Ima Miri
– Ima Miri, Commented Aug 13, 2019 at 7:50
So it seems that :skillsis of type bytea. I am not too deep into Hibernate but it seems that Hibernate does not support array types. Maybe this helps? vladmihalcea.com/… — S-Man
– S-Man, Commented Aug 13, 2019 at 7:56
@S-Man is the type of skills in your table? can yous show us both entity and table schema please — Youcef LAIDANI
– Youcef LAIDANI, Commented Aug 13, 2019 at 8:09
If skills contains binary data (which is what bytea means), then why would you search for text strings in that column? — user330315
– user330315, Commented Aug 13, 2019 at 8:18

Youcef LAIDANI · Accepted Answer · 2019-08-13 07:53:51Z

2

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)

answered Aug 13, 2019 at 7:53

Youcef LAIDANI

60.2k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

S-Man Over a year ago

For me it seems that both sides are arrays. In this case this would not work

Youcef LAIDANI Over a year ago

@S-Man why It should not work, take a look rextester.com/QSO46205

S-Man Over a year ago

Yes, but I thought that the left-handed term is an array too: dbfiddle.uk/… (I made this assumption because of the ANY(skills) part in the query)

Youcef LAIDANI Over a year ago

@S-Man This is not clear in the question, I will ask the question

Ima Miri Over a year ago

@S-Man yes, both sides are array and I've tried that where clause condition and it didn't work

|

coladict · Accepted Answer · 2019-08-13 08:21:29Z

1

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.

answered Aug 13, 2019 at 8:21

coladict

5,1151 gold badge20 silver badges31 bronze badges

1 Comment

Ima Miri Over a year ago

Both didn't work. Got this error: Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: text[] @> bytea Hint: No operator matches the given name and argument types. You might need to add explicit type casts. I think the issue is because of String[] but I don't know how to fix it.

Chen · Accepted Answer · 2021-12-02 15:43:59Z

0

where array_position(skills, :skills) > 0

it could be also your db mapping to entity problem,

test with @Transit for entity property

edited Dec 2, 2021 at 15:43

answered Dec 1, 2021 at 12:28

Chen

776 bronze badges

1 Comment

christopher.online Over a year ago

Please use code formatting/markup for your answer to improve readability

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related