Skip to main content

expand

1. EXPAND Function​

The EXPAND function in H2 database is used to generate a result set by expanding the elements of an ARRAY.

2. Syntax​

The syntax for the EXPAND function in H2 database is as follows:

EXPAND(array_expression)

Arguments​

  • array_expression: An expression that evaluates to an ARRAY. This can be a column, a subquery, or a literal array.

Return​

  • The EXPAND function returns a result set with one row for each element in the array. The result set contains a single column named VALUE which represents the expanded elements of the array.

3. Notes​

  • The EXPAND function in H2 database only works with ARRAY data types. If you try to use it with other data types, it will result in an error.
  • The order of the elements in the result set is not guaranteed unless ordered explicitly using an ORDER BY clause.
  • If the input array is empty, the EXPAND function will return an empty result set.
  • It's important to note that the EXPAND function is a specific feature of the H2 database and may not be available in other databases.

4. Examples​

Here are a few examples demonstrating the usage of the EXPAND function in H2 database:

Example 1 - Expanding an array literal:​

SELECT EXPAND(ARRAY[1, 2, 3, 4]) AS value;

Output:

value
-----
1
2
3
4

Example 2 - Expanding an array column:​

CREATE TABLE numbers (
  id INT PRIMARY KEY,
  vals ARRAY
);

INSERT INTO numbers VALUES (1, ARRAY[1, 2, 3]), (2, ARRAY[4, 5]);

SELECT id, EXPAND(vals) AS value
FROM numbers;

Output:

id | value
---+-------
1  | 1
1  | 2
1  | 3
2  | 4
2  | 5
  • None. The EXPAND function is specific to H2 database and doesn't have direct equivalents in other databases.