Using ANY to search array via hibernate

Integrations
1

Hi,

I am trying to execute a native sql query from spring hibernate application which looks like:
String query = “Select i.providercd as alternatives from doc.insurers i where ANY(i.products[‘name’]) = :productcd”;
Object obj = em.createNativeQuery(query).setParameter(“productcd”, productcd).getResultList();

But it fails at execution time with the error below:
2019-04-23 16:18:20.192 ERROR 18816 — [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: SQLParseException: line 1:63: no viable alternative at input ‘Select i.providercd as alternatives from doc.insurers i where ANY’
2019-04-23 16:18:20.206 ERROR 18816 — [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet] with root cause

io.crate.shade.org.postgresql.util.PSQLException: ERROR: SQLParseException: line 1:63: no viable alternative at input ‘Select i.providercd as alternatives from doc.insurers i where ANY’

2

As documented here https://crate.io/docs/crate/reference/en/latest/general/builtins/array-comparisons.html#any-some-array-expression, the correct syntax is expression operator ANY(...), using ANY(...) on the left-hand side is not supported.

So in your case, this should work:
Select i.providercd as alternatives from doc.insurers i where :productcd = ANY(i.products[‘name’])

3

Thank you. It worked by moving ANY to the right side of the condition.