A data engineer is building a transformation that processes a `products` table containing a `tags` column of type `ARRAY`. They need to create one row per tag so each tag can be analyzed individually. Which SQL function should they use?

Question

A data engineer is building a transformation that processes a `products` table containing a `tags` column of type `ARRAY<STRING>`. They need to create one row per tag so each tag can be analyzed individually. Which SQL function should they use?

Accepted Answer

B. EXPLODE(tags). The `EXPLODE` function in Spark SQL (and Databricks SQL) takes an array or map column and generates a new row for each element in the array. For a `tags` column of type `ARRAY<STRING>`, `SELECT id, EXPLODE(tags) AS tag FROM products` would produce one row per tag per product. `FLATTEN` unnests nested arrays into a single array but does not create new rows. `UNNEST` is a PostgreSQL function not available in Databricks SQL. `ARRAY_AGG` aggregates values into an array, which is the opposite operation.

A data engineer is building a transformation that processes a `products` table containing a `tags` column of type `ARRAY<STRING>`. They need to create one row per tag so each tag can be analyzed individually. Which SQL function should they use?

Related Questions

Discussion

A data engineer is building a transformation that processes a products table containing a tags column of type ARRAY<STRING>. They need to create one row per tag so each tag can be analyzed individually. Which SQL function should they use?

Related Questions

Discussion

A data engineer is building a transformation that processes a `products` table containing a `tags` column of type `ARRAY<STRING>`. They need to create one row per tag so each tag can be analyzed individually. Which SQL function should they use?