Unnesting multiple array columns
509 views
Skip to first unread message
Abeleshev Artem
Sep 16, 2023, 4:45:13 PM9/16/23
to H2 Database
Good day!
I've checked on H2 manual and found that H2 supports unnesting of multiple arrays (http://www.h2database.com/html/functions.html#unnest). For example:
select * from unnest(array[1, 2, 3], array['one', 'two', 'three']);
gives desired results:
1, one
2, two
3, three
But unnest supports only array expressions, not array columns. I'm not quite sure I understand what is an array expression. Let's say I defined following table and single value:
create table foo (
bar integer array,
baz character varying array
);
insert into foo (bar, baz) values (array[1, 2, 3], array['one', 'two', 'three'])
I can unnest each single column by executing following commands:
select * from unnest(select bar from foo);
select * from unnest(select baz from foo);
returning expected results:
1
2
3
one
two
three
So I supposed that expression "select bar from foo" returns an array expression (since it is accepted by the function). But when I tried to apply the same for multiple columns it fails:
select * from unnest(select bar from foo, select baz from foo);
results in an error:
Syntax error in SQL statement "select * from unnest(select bar from foo, [*]select baz from foo)"; expected "identifier"; SQL statement:
select * from unnest(select bar from foo, select baz from foo) [42001-222] 42001/42001
So, my question is: is it possible to unnest multiple array columns in H2?
Thanks!
With respect,
Artem
I've checked on H2 manual and found that H2 supports unnesting of multiple arrays (http://www.h2database.com/html/functions.html#unnest). For example:
select * from unnest(array[1, 2, 3], array['one', 'two', 'three']);
gives desired results:
1, one
2, two
3, three
But unnest supports only array expressions, not array columns. I'm not quite sure I understand what is an array expression. Let's say I defined following table and single value:
create table foo (
bar integer array,
baz character varying array
);
insert into foo (bar, baz) values (array[1, 2, 3], array['one', 'two', 'three'])
I can unnest each single column by executing following commands:
select * from unnest(select bar from foo);
select * from unnest(select baz from foo);
returning expected results:
1
2
3
one
two
three
So I supposed that expression "select bar from foo" returns an array expression (since it is accepted by the function). But when I tried to apply the same for multiple columns it fails:
select * from unnest(select bar from foo, select baz from foo);
results in an error:
Syntax error in SQL statement "select * from unnest(select bar from foo, [*]select baz from foo)"; expected "identifier"; SQL statement:
select * from unnest(select bar from foo, select baz from foo) [42001-222] 42001/42001
So, my question is: is it possible to unnest multiple array columns in H2?
Thanks!
With respect,
Artem
Evgenij Ryazanov
Sep 16, 2023, 11:10:08 PM9/16/23
to H2 Database
Hello!
Yes, it is possible, but with valid SQL only.
Subqueries must be enclosed in parentheses, all your queries are incorrect. Valid queries are
select * from unnest((select bar from foo));
select * from unnest((select baz from foo));
Yes, it is possible, but with valid SQL only.
Subqueries must be enclosed in parentheses, all your queries are incorrect. Valid queries are
select * from unnest((select bar from foo));
select * from unnest((select baz from foo));
select * from unnest((select bar from foo), (select baz from foo));
In some cases H2 allows subqueries without parentheses due to historic reasons, but this undocumented syntax was implemented very inconsistently and actually it isn't possible to implement it in reliable way due to syntax conflicts. Don't use it, it is not supported and it may not work in future versions of H2 in places where it works in 2.2.222.
Abeleshev Artem
Sep 17, 2023, 4:08:44 AM9/17/23
to H2 Database
Thank you for the answer. It works as expected.
But I realized that it will not solve my original problem I have in mind. Actually, I'm tryting to come up with some more or less ANSI compliant solution for the following issue:
I have two (or more) columns in database that represents the state of some single complex object. Taking an example from above, let's imagine that "bar" and "baz" are parts of one object. In case of "foo" contains scalar value (not collection) of the object, both columns "bar" and "baz" will have scalar types (not array):
create table foo (
id varchar(256) primary key,
bar integer,
baz varchar(256)
);
and I can select using following query:
select * from foo where bar = ... and baz = ...
But in case of "foo" contains collection of the this objects, both columns "bar" and "baz" become array types:
create table foo (
id varchar(256) primary key,
bar integer array,
baz varchar(256) array
);
and if I select using following query:
select * from foo where ... = any(bar) and ... = any(baz)
it will give me false matches, as I need to compare only values from both arrays of the same ordinality.
While unnest in H2 supports unnesting of multiple arrays, it is impossible to use arrays taken from the currently selected row (it seems it is unable to reference columns of outside query from unnest finction). like this:
select * from foo where exists (select 1 from unnest ((select bar from foo where id = foo.id), (select baz from foo where id = foo.id)) as e(e1, e2) where e1 = '...' and e2 = '...')
In PostgreSQL referencing columns of curretly selected row from unnest is possible though. As well as it supports more clean way, allowing to use column references directly instead of array expressions within the unnest function:
select * from foo where exists (select 1 from unnest (bar, baz) as e(e1, e2) where e1 = '...' and e2 = '...')
BTW, Is there are any plans to support latest?
Thanks!
With respect,
Artem
0 new messages