Answer-first summary for fast verification
Answer: SELECT JSON_EXTRACT(product_info, '$.price') as price, CAST(JSON_EXTRACT(product_info, '$.price') AS DOUBLE) as double_price FROM dataset
Option B is the correct answer because it accurately uses the JSON_EXTRACT function to retrieve the 'price' field from the JSON objects within the 'product_info' column and then correctly casts this field to a double type. Option A incorrectly attempts to use dot notation directly on the JSON column, which is not valid for JSON field extraction in Spark SQL. Option C uses an incorrect syntax for JSON field extraction, as square brackets are not the appropriate method for this operation. Option D fails to specify the extraction of the 'price' field from the JSON column before attempting the cast, leading to an error.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
In a scenario where you are working with a large dataset stored in Azure Databricks, you encounter a 'product_info' column that contains JSON objects. Each JSON object includes various product details, among which is a 'price' field stored as a numeric value. Your task is to write a Spark SQL query that extracts the 'price' field from the JSON objects, casts it to a double type, and creates a new table displaying both the original 'price' and the newly casted 'double_price'. Considering the need for accuracy and efficiency in handling JSON data within Spark SQL, which of the following queries correctly accomplishes this task? (Choose one option)
A
SELECT product_info.price, CAST(product_info.price AS DOUBLE) as double_price FROM dataset
B
SELECT JSON_EXTRACT(product_info, '.price') AS DOUBLE) as double_price FROM dataset
C
SELECT product_info['price'], CAST(product_info['price'] AS DOUBLE) as double_price FROM dataset
D
SELECT price, CAST(price AS DOUBLE) as double_price FROM dataset