Working with arrays and nested data structures in SQL can be challenging, but thankfully, SQL provides functions that let us manipulate these structures with ease. One such function is UNNEST. If you’ve ever dealt with arrays in SQL and needed to transform them into rows, UNNEST is your go-to function.
What is UNNEST in SQL?
The UNNEST function in SQL is used to convert an array or nested data structure into a set of rows. This is incredibly useful when working with databases that support array data types, such as PostgreSQL, BigQuery, or other modern relational databases.
Essentially, UNNEST takes an array and returns a relation (set of rows), where each row represents an element from the array.
Why Use UNNEST?
There are several reasons why UNNEST is a powerful function:
- Allows you to work with array fields efficiently.
- Makes it easier to join array elements with other tables.
- Useful for flattening hierarchical or JSON-like structures.
Basic Syntax
The syntax of UNNEST is quite simple. Here’s a basic example in PostgreSQL:
SELECT UNNEST(ARRAY[1, 2, 3, 4, 5]);This query will return:
| unnest |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Using UNNEST with a Table Column
Now, let’s look at a real-world scenario where UNNEST is applied to a column in a table.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
favorite_numbers INTEGER[]
);
INSERT INTO users (name, favorite_numbers) VALUES
('Alice', ARRAY[3, 7, 15]),
('Bob', ARRAY[2, 4, 6]);If we run the following query:
SELECT name, UNNEST(favorite_numbers) AS number FROM users;We get:
| name | number |
|---|---|
| Alice | 3 |
| Alice | 7 |
| Alice | 15 |
| Bob | 2 |
| Bob | 4 |
| Bob | 6 |
Using UNNEST with Multiple Arrays
What if we have multiple arrays that we want to unnest together? We can use UNNEST with multiple columns:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
item_names TEXT[],
item_prices NUMERIC[]
);
INSERT INTO orders (item_names, item_prices) VALUES
(ARRAY['Book', 'Pen'], ARRAY[12.99, 1.99]),
(ARRAY['Laptop', 'Mouse'], ARRAY[1200.00, 25.00]);Now, when using UNNEST on both arrays:
SELECT order_id, UNNEST(item_names) AS item, UNNEST(item_prices) AS price FROM orders;We get the pairs correctly mapped:
| order_id | item | price |
|---|---|---|
| 1 | Book | 12.99 |
| 1 | Pen | 1.99 |
| 2 | Laptop | 1200.00 |
| 2 | Mouse | 25.00 |
Common Mistakes When Using UNNEST
When working with UNNEST, it’s easy to make a few common mistakes:
- Forgetting to maintain element order: If arrays are unnesting separately, ensure they are mapped correctly.
- Not joining unnested results back: If needed, make sure to re-aggregate results when necessary.
- Using UNNEST on an empty array: Always check for empty arrays to prevent unexpected results.
When to Use (and Not Use) UNNEST
Using UNNEST is ideal for:
- Flattening array fields into separate rows.
- Joins that require array elements to be separate.
- Transforming nested data structures into relational formats.
However, UNNEST may not be ideal when:
- Working with very large arrays that could create performance issues.
- Needing to preserve array structures for later aggregations.
Final Thoughts
The UNNEST function in SQL is incredibly powerful when dealing with arrays and structured data. Whether you’re working with PostgreSQL, BigQuery, or other databases supporting arrays, mastering UNNEST will help you efficiently handle complex data.
Other interesting article:
How ARRAY_AGG works in SQL? Best ARRAY_AGG examples