UNNEST in SQL

UNNEST is a SQL function that expands a nested array or list into a set of rows. Each element of the array becomes a separate row in the result set. It’s particularly useful when dealing with data that has been stored in a denormalized format, such as JSON or other semi-structured data types, where a single record can contain multiple values within an array.

The basic syntax of UNNEST is straightforward:

SELECT
  t.column1,
  t.column2,
  unnested_element
FROM
  your_table AS t,
  UNNEST(t.array_column) AS unnested_element;

In this example, t.array_column is the column containing the array you want to expand. The UNNEST function takes this array and, for each row in your_table, it generates a new row for every element in that array. The alias unnested_element is then used to reference the individual array elements.

Example 1: Simple Array of Strings

Imagine you have a table called users with a column favorite_foods which is an array of strings. You want to create a list of all individual favorite foods and the user ID who likes them.

To display each user ID alongside their individual favorite food.

SELECT
  user_id,
  food
FROM
  users,
  UNNEST(favorite_foods) AS food;

• Explanation: This query takes the favorite_foods array for each user and flattens it, creating a new row for each item in the array. The alias AS food gives a name to the column containing the unnested values.

Example 2: Array of Structs

You have a table called products with a column reviews which is an array of structs. Each struct contains reviewer_name and rating. You need to find all product IDs that have a review with a rating of 5.

Get Kandaanusha’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To list the product_id and the reviewer_name for all reviews that have a rating of 5.

SELECT
  product_id,
  review.reviewer_name
FROM
  products,
  UNNEST(reviews) AS review
WHERE
  review.rating = 5;

• Explanation: We UNNEST the reviews array of structs, treating each review as a single row. We then access the nested fields using dot notation (review.reviewer_name and review.rating) to filter and select the desired information.

Scenario 3: Correlated UNNEST with a JOIN

You have a store_sales table with a sales_rep array of strings, and a separate employees table with employee_name and commission_rate. You want to calculate the total commission for each sales representative, assuming each sale is associated with one or more reps.

Question: Write a SQL query that joins store_sales and employees to calculate the total commission for each sales representative, using UNNEST to handle multiple reps per sale.

SELECT
  e.employee_name,
  SUM(s.sale_amount * e.commission_rate) AS total_commission
FROM
  store_sales AS s,
  UNNEST(s.sales_rep) AS rep_name
JOIN
  employees AS e ON rep_name = e.employee_name
GROUP BY
  e.employee_name;

• Explanation: In this query, the UNNEST is correlated with the store_sales table. For each row in store_sales, the sales_rep array is unnested. The resulting individual rep_name is then used to join with the employees table. This allows the commission_rate for each representative to be correctly applied to the sale_amount before aggregation. This is an example of an inner UNNEST join where the UNNEST is placed in the FROM clause.

Scenario 4: Using UNNEST with WITH OFFSET

You have a table called blog_posts with a column keywords which is an array of strings. You want to display each keyword and its position in the array (its index).

Question: Write a SQL query to display the post_id, each keyword, and its index (starting from 0) within the original array.

SELECT
  post_id,
  keyword,
  index AS keyword_index
FROM
  blog_posts,
  UNNEST(keywords) WITH OFFSET AS keyword
ORDER BY
  post_id,
  keyword_index;

• Explanation: The WITH OFFSET clause is used with UNNEST to return the ordinal position (the index) of each element in the unnested array. The alias AS keyword is used for the unnested elements, and index is the default column name for the offset. This is particularly useful for scenarios where the order of elements in the array is significant.