Databricks Certified Data Analyst Associate · Free Practice Question Easy
Question 7
An analyst needs to flatten a nested array structure within the order_items column of the orders table, so that each item appears as a separate row. Which function should be used to complete the following SQL query?
- SELECT order_id, _______(order_items) AS item
- FROM orders;
-
A
json_tupleB)
-
B
cast -
C
group_by -
D
from_json -
E
explode
Reveal correct answer
Correct answer: E
Explanation
explode (Correct Answer): The explode() function is specifically designed to flatten nested arrays or maps, turning each element in the array into a separate row.
json_tuple: This function is used for extracting values from a JSON object, not for flattening arrays.
group_by: This clause is used for aggregating data based on groups, not for working with nested structures.
from_json: This function is used to parse JSON strings into a structured format based on a schema but does not flatten arrays.
cast: This function is used to change the data type of a column, not for handling nested data structures.
The correct function to use in this scenario is explode, as it allows the analyst to transform each element of the order_items array into individual rows in the orders table.
References:
https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/explode
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
