Ability to query properties from a json column in a DeltaTable

[gopala000]

I have a Delta Table that has many columns that are structured and one column that has json stored as a string. Does Fabric has the ability to expose properties in json as table fields to query (like DataBricks). If not, please provide guidance on how to do it.

Basically I need to confirm if I need to add columns that I need from json to the data table during ingestion after parsing the json using from_json() and save it to the Delta table or is there a better option available?

Looks like parsed json column is stored as Any datatype while the original json column is a string data type. Is there any advantage in storing the parsed json column or should I remove it before saving the table?

Thanks!

[Anonymous · Accepted solution]

Hi @gopala000 

You can use the from_json() function to parse a JSON string column into a structured format. The specific steps are as follows:

Create a new Lakehouse in the Microsoft Fabric workspace, or select an existing Lakehouse.

In the Lakehouse, upload your data file and import the data into the Delta table in the Lakehouse.

Then select "Open notebook" —> "New notebook"

Select "Spark SQL"

This allows you to extract specific properties from JSON and store them as separate columns.

If you need to frequently query specific properties in JSON data, it is recommended to parse the JSON and add the necessary columns during ingestion. This will make querying more efficient and simpler.

The parsed JSON column is stored as Any data type, which is useful if you need to preserve the entire JSON structure for future use. However, if you only need specific properties, you may not need to store the parsed JSON column. Instead, you can extract and store only the required properties as separate columns.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.