Skip to main content

unnest

1. UNNEST Function​

The UNNEST function in H2 database is used to expand an array or a nested array into a table format.

2. Syntax​

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

UNNEST(array_expression) [AS column_alias]

Arguments​

  • array_expression: The array or nested array that needs to be expanded into a table format.

Return​

  • The UNNEST function returns a table with a single column containing the elements from the input array or nested array.

3. Notes​

  • The UNNEST function in H2 database requires an array or nested array as input. If the input is not an array or nested array, an error will be thrown.
  • The UNNEST function can be used in conjunction with other table functions or queries to transform the data in a more meaningful way.
  • The column alias is optional. If provided, it is used to assign a name to the column in the resulting table. If not provided, the column will be named "COLUMN".

4. Examples​

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

Example 1 - Expanding a simple array:​

SELECT UNNEST(ARRAY[1, 2, 3, 4]) AS num;

Output:

num
---
1
2
3
4

Example 2 - Expanding a nested array:​

SELECT UNNEST(ARRAY[ARRAY[1, 2], ARRAY[3, 4]]) AS num;

Output:

num
---
[1, 2]
[3, 4]

Example 3 - Using UNNEST in a JOIN operation:​

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  skills ARRAY
);

INSERT INTO employees VALUES (1, 'John', ARRAY['Java', 'SQL']);
INSERT INTO employees VALUES (2, 'Jane', ARRAY['JavaScript', 'HTML']);

SELECT e.id, e.name, u.skill
FROM employees e
JOIN UNNEST(e.skills) AS u(skill) ON 1 = 1;

Output:

id | name |    skill
---+------+------------
1  | John | Java
1  | John | SQL
2  | Jane | JavaScript
2  | Jane | HTML
  • None in H2 database.