Parsing: Nested JSON Objects

Dalton_Ruer · Nov 3, 2025 2:10:57 PM

The image depicts what is called a nested JSON structure containing a variety of both entities and field and value pairs. If I asked you what the sheriff's name was, you would instinctively say "Marshal Ada Lovelace."

Unlike the previous post Parsing: Flat JSON - Field Value Pairs this structure has more than just the fields and values it has entity structure. The name field isn't a standalone name, it's part of the sheriff entity. The sheriff's office isn't just a field ... it's a nested entity, that includes multiple field value pairs.

JsonGet()

In the previous post I referred to above, I introduced you to a function called JsonGet. It allows you to give it a JSON block and ask it for the value of a specified field. Your likely question is "How do I ask it for a field that might be in a nested entity of another entity?"

The answer is by simply providing the qualified path to the field name. Just as I asked you what was the "sheriff's name," you would call the JsonGet() function and ask it for the /sheriff/name field. Assume that the JSON structure from the first image were loaded into a field called Nested_JSON_Block in a table called Nested. The following code would load all 4 of these unstructured data values into structured fields.

SQL Like

If you are familiar with SQL then this notion of qualifying the field name is nothing new to you. If you issue a Select statement from a single table all you need to do is list the field names in the select clause.

Select FieldA, FieldB, FieldC

From Table1

But when you join tables, especially if you have a field name in more than 1 table, you have no choice but to qualify the path for the field

Select Table1.FieldA, Table1.FieldB, TableA.FieldC

From Table1 ... blah blah blah join Table2

Instead of table.field notation like SQL, the JsonGet() function within Qlik Sense simply needs the path in /entity/field notation. If you have nests of nests of nests, it is the wild west of JSON data you might be pulling, that notation would continue /entity1/entity2/entity3/entity4/field.

Practice

If you are anything like me you don't like to assume anything, and you are a visual or experiential learner. So, go ahead and download the attached WildDataFrontier.qvf that is attached, upload it to your environment and open the load script.

{ Notice there are multiple sections. Each of them will pertain to a separate article and for this article the section named "2 - Nested JSON: Nested Objects" is the one you want to have at the top of your script for this post. }

Each section will begin with a common pattern. I will show you the prettified version of the JSON structure. Meaning it's indented, and thus easy to read. Then I will remove the line feeds so that the JSON structure being covered is a single line that we deal with as a single string for transformation within Qlik Sense. That single text/block is what API's will return or what you will read out of textual fields that contain JSON blocks from RDMBS systems.

For your learning, simply modify any/all of the field names, modify the JsonGet transformations and then load the data. Try adding:

A date field to indicate when the sheriff became the sheriff.
A sheriff office fax number.
If you really want to be brave ... add a new entity for the sheriff called spouse. The spouse entity should have a married true/false value, and spouses name and their marriage date.
If all that was to easy for you, feel free to really jump into it and add a new entity for the office entity called statistics. The sheriff/office/statstics entity should have fields like number of deputies, number of jail cells.

Preview

As you go through your changes, the easiest thing to do is visualize the Preview at the bottom of the load editor.

Hint

As you try and make your changes if editing line 19 is to confusing and you struggle with the nesting, feel free to make your changes instead in the area that is commented out between lines 4-11. Then copy that prettifield block of code and change line 19 to only have the opening and closing single quote marks. Paste your copied block after the first single quote and start removing the line feeds. That's how I built the code for you so no shame in taking the easy route. 😁

Big Picture

This post is part of a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.

Posts: