Databricks Data Engineer Associate Question 175
Single answerYou are working with a Delta table containing a column named raw_json that stores JSON strings. You need to extract the user_id and timestamp fields from these JSON strings into separate columns using PySpark. Which of the following code snippets correctly parses the JSON and extracts the required fields?
- A
df.withColumn('parsed', from_json(col('raw_json'), 'user_id STRING, timestamp STRING')).select(col('parsed.user_id'), col('parsed.timestamp'))
- B
df.withColumn('user_id', get_json_object(col('raw_json'), '$.user_id')).withColumn('timestamp', get_json_object(col('raw_json'), '$.timestamp'))
- C
df.selectExpr('json_tuple(raw_json, "user_id", "timestamp") AS (user_id, timestamp)')
- D
df.withColumn('user_id', col('raw_json.user_id')).withColumn('timestamp', col('raw_json.timestamp'))
Show answer and explanation
Correct answer: B
Explanation
The get_json_object function is a common and correct way to extract specific fields from a JSON string in PySpark. It requires specifying the JSONPath for the fields to be extracted. Other options are either syntactically or logically incorrect for parsing JSON strings into separate columns.
- A. Incorrect.
This code uses the
from_jsonfunction, but the schema provided as a string ('user_id STRING, timestamp STRING') is invalid. The schema should be defined usingStructTypeor a valid DDL string. - B. Correct.
This code correctly uses the
get_json_objectfunction to extract theuser_idandtimestampfields from the JSON strings stored in theraw_jsoncolumn. - C. Incorrect.
The
json_tuplefunction does not allow aliasing the extracted fields directly into new columns. It is not used correctly in this snippet. - D. Incorrect.
This code assumes that the
raw_jsoncolumn is already a struct, which is incorrect because it is a JSON string. Therefore, accessing fields usingcol('raw_json.user_id')will fail.