Search for a command to run...
Using Panel, Bokeh to interactively explore JSON structure
Principal Program Manager, Microsoft Fabric CAT helping users and organizations build scalable, insightful, secure solutions. Blogs, opinions are my own and do not represent my employer.
How can we automate this in to a pipeline? We have nested JSON messages arriving into event hubs and we have performance issues while parsing JSON and load into delta tables
Last week Microsoft released an open-source text embedding model called Harrier in three sizes- 270M, 0.6B and 27B. I have been testing it in my RAG pipeline and so far it has crushed all my metrics.
For Power BI Copilot and Data agents with semantic models, you must use Prep Data for AI configuration to ground the responses in the context added in Prep for AI. In this blog, I will show you how yo
At FabCon Atlanta last week, the updated notebook Copilot for data engineering and data science was announced. It brings agentic capabilities to the Copilot and is much more intelligent and Fabric-awa
Using Fabric data agent Python SDK
Use workspace monitoring to monitor MCP server use
JSON is ubiquitous, particularly when working with APIs and logs. Its unstructured nature makes it highly flexible for handling anything from a simple array to a complex nested structure. However, this can also make it challenging for data analysis. When parsing JSON, it's crucial to understand its structure so you can flatten it and convert it into a tabular format for analysis. Once the structure is identified, you can use pandas or PySpark to explode or normalize it into the desired shape. In this article, I will explain the method I use. While this approach is applicable to any notebook, there is a specific trick to make it work in a Fabric notebook.
I will use the following example. Just looking at it, we can see that it's about employees, their contact details and skills.
{
"employees": [
{
"id": 1,
"name": "John Doe",
"role": "Developer",
"contact": {
"email": "john.doe@example.com",
"phone": "123-456-7890"
},
"skills": ["Python", "JavaScript"]
},
{
"id": 2,
"name": "Jane Smith",
"role": "Designer",
"contact": {
"email": "jane.smith@example.com",
"phone": "098-765-4321"
},
"skills": ["Adobe XD", "Figma"]
}
]
}
If you use .json_normalize() method in pandas to read this json, you will get a dataframe with just one row and column.
To unnest this, we will need to identify the structure and pass extra arguments to .json_normalize() . I like using panel library for that. With panel, you can identify the structure easily and also interact with it to find the nested objects. You just need one trick to make it work in Fabric notebook.
First, install panel and instantiate the extension
!pip install panel --q
import panel as pn
pn.extension()
Next, use the .pane.JSON() method to load the json
pn.pane.JSON(json_obj, name='JSON', height = 300, width = 300)
After executing the code above, you would expect to see the JSON structure, but it doesn't appear. This is the trick. Panel uses Bokeh renderer and hence you need to import Bokeh to output the visual. I wrote about it in one of my blogs.
Fabric runtime already has Bokeh installed so you just need to import it.
import bokeh
bokeh.io.output_notebook()
pn.pane.JSON(json_obj, name='JSON', height = 300, width = 300)
With this, you should now see the panel visual:
This is fully interactive and you can see all levels of the json and the its nested structure.
From this we can see two things, we have employee records under the key "employees". Each employee record is a dictionary with keys for 'ids', 'name', 'role' and nested dictionaries for 'contact' and a list for 'skills'. To flatten this to a tabular format , we need to flatten the list under 'skills' and extract attributes under the 'contact' dictionary. Lists can be flattened by record_path argument and nested dictionary can be flattened by meta . This will transform the nested and hierarchical json into a table.
df = (pd.json_normalize(json_obj['employees'],
record_path='skills',
meta=['id', 'name', 'role', ['contact', 'email'], ['contact', 'phone']],
record_prefix='skill_')
)
df
This was rather a simple example, but you can use a similar approach to plot the structure, interact with it and then flatten it as needed. To recap, the trick to make it work in Fabric notebook was to import bokeh and including bokeh.io.output_notebook() in the code block.
If you are using the Synapse VSCode extension to work with Fabric notebooks, try the JSON crack extension. It plots the json into a fully interactive tree.