I have an H2 column with type TEXT ARRAY.

CREATE TABLE app.worklist
(
    id INT,
    profession TEXT ARRAY
);

I would like to query if any array element matches a LIKE parameter.

The following works in Postgres:

SELECT * FROM app.worklist
 WHERE EXISTS (
    SELECT
    FROM unnest(profession) AS p
    WHERE p LIKE 'A%'
);

However, it fails in H2 .h2.jdbc.JdbcSQLSyntaxErrorException: Column "PROFESSION" not found; due to a long unfixed bug.

How can I accomplish this in H2?

I have an H2 column with type TEXT ARRAY.

CREATE TABLE app.worklist
(
    id INT,
    profession TEXT ARRAY
);

I would like to query if any array element matches a LIKE parameter.

The following works in Postgres:

SELECT * FROM app.worklist
 WHERE EXISTS (
    SELECT
    FROM unnest(profession) AS p
    WHERE p LIKE 'A%'
);

However, it fails in H2 .h2.jdbc.JdbcSQLSyntaxErrorException: Column "PROFESSION" not found; due to a long unfixed bug.

How can I accomplish this in H2?

• sql
• arrays
• h2

Share Improve this question Follow asked Nov 20, 2024 at 18:39 patstuartpatstuart 1,98811 gold badge2020 silver badges3030 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

After days of research, I determined that it is not possible to do this cleanly in H2.

Our team used a workaround. When the data is ingested, we flatten the array.

CREATE TABLE app.worklist
(
    id INT,
    profession VARCHAR(65536)
);

We joined each element with control character 0x1a. ["foo", "bar"] becomes "{0x1a}foo{0x1a}bar{0x1a}"

val profession = professionArray.joinToString(
   separator = "\u001A",
   prefix = "\u001A",
   postfix = "\u001A",
)

To search, we execute:

SELECT *
   FROM app.worklist
   WHERE profession LIKE '{0x1a}A%';

This solution only works if

1. you know the length of your data.
2. you know the the control character won't be present in the original text array.
3. you are looking for 'A%', '%A', or '%A%'; it doesn't work for combined expressions like 'A%B'.

SELECT * 
FROM app.worklist, TABLE(profession) AS p(element)
WHERE element LIKE 'A%';