Flattening json strings columns

Information: product data often includes detailed attributes stored in complex formats like JSON strings. These attributes might include product specifications, clinical trial information, regulatory approval statuses, and other critical details required for analysis, reporting, and regulatory compliance. The purgo_playground.d_product_revenue table contains a product_details column with complex JSON strings. We need to select each field from this product_details JSON string column. sample value of product_details is given below:

 

 

 

Requirement: Write a Databricks SQL query to select each fields from product_details JSON String column in the d_product_revenue table, which contains data structured like this: 

 

{ 

 

  "batch_number": "BATCH2024-5678", 

 

  "expiration_date": "2025-12-31", 

 

  "manufacturing_site": "Site A", 

 

  "regulatory_approval": "Approved", 

 

  "price": 250.75 

 

} 

 

 Unity Catalog Information: purgo_playground.d_product_revenue. 

 

Expected output: Databricks SQL query