Search code examples
javasqlarraysselecthsqldb

Select row by array contains check

I have a table like

CREATE TABLE table(
    name VARCHAR(100) NOT NULL,
    array INTEGER ARRAY NOT NULL
);

For example if I have the rows

name: test, array: [1, 2, 3]
name: test2, array: [663, 332, 334]

How can I select the row which contains 332 in array? The array can be of any length but never null.

Unfortunately there isn't much documentation on HSQLDB arrays.. I'm using version 2.5.1 with Java JDBC.

Solution

  • Solved it. There was an exception caused by a previous method call which prevented the actual exception from throwing.

    SELECT * FROM table WHERE 332 IN ( UNNEST(array) )