Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner

Dalton_Ruer · Nov 4, 2025 9:42:17 AM

As you look at the image above you will notice multiple JSON objects. In the previous post Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner we walked through how we could easily handle this type of data in a fixed way. All you need to do is know every single field:value pair that will ever come to you.

Flexibility

If you haven't already read that previous post be sure to do so and go through the practice. You need to understand that while easy to maintain for new fields that come across ... the thought of knowing every single field:value pair that will ever come to you is kind of daunting. Right?

I kind of slipped it in there but:

You know that you can't see over the horizon to know which bandits are heading to your town.
You also know that while the job security is great, constantly being behind and having to rush to identify the new bandits can cause a lot of stress.

This post will walk you through the same data that we dealt with in the previous post, but will allow you to read all of the values for all fields that exist now, and those that might come riding in to town tomorrow.

Practice

If you haven't already done so for a previous post, go ahead and download the WildDataFrontier.qvf that is attached, uploaded 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 "5 - Mixed JSON: Flexible Values" is the one you want to have at the top of your script for this post. }

Same basic data as the previous post, but I've also included the 4'th row of data that I asked you to do as part of the practice. The flexibility that you need as a data sheriff in this JSON Wild Data Frontier is going to be handled by the incredible flexibility available to you in how Qlik Sense can load and transform that data.

In previous posts we've simply done a resident load to transform the data directly in 1 step using that ever so flexible JsonGet() function. In this post our transformation is going to do a few things that may be new to you. Be sure to click the links for each to get at least an introduction to them:

Working backwards

Once you have the application open and have moved section "5 - Mixed JSON: Flexible Values" to the top of the script go ahead and load the data. We are going to start by previewing the data for the finished product and then work backwards to understand how it was accomplished. If you look at the preview screen you will notice something odd: You have the Mixed_JSON table that was built by the inline load part of the script, then you have a whole bunch of other tables. One for each of the fields that is part of the data.

Go ahead and select the Information_Flexible.bounty table so you can preview what data is in that table.

If you walk each of the tables you will notice that each table contains the value for that given field. If you look at the Data Model Viewer you will see that every table is associated based on the row number. Which means in any chart that you visualize you will easily be able to present the values for all of the fields.

Add Data

Add the following row of data to the inline load script, and then reload the data. Notice that it is brand new type and contains a value for a new field called bowtie_color, as well as values for two existing fields text and bounty.

5, '{ "type": "dork_mail", "bowtie_color": "Qlik Green", "text" : "Qlik Dork is coming to town", "bounty": 1000000 }'

What do you know? Our application is indeed flexible and it has created a new table called Information_Flexible:bowtie_color

Feel free to check out the values for it (as well as the values for the text and bounty tables) to see that indeed we have created structure from this unstructured heterogeneous JSON mess.

How

I tried my best to include comments in the load script which will hopefully aid in your learning. But I will admit sometimes it's hard to mentally walk through a script like this, even with comments, when there are multiple preceding loads like this. So, let's take this one step at a time and walk through what happens for each of the preceding load steps. To do this highlight rows 20-43 (from the Generic Load line until before Step1) and then comment those rows out and then reload the data.

If you look at the preview window now you will see that you have a single Information_Flexible table. As described in my comments, all step 1 did was to remove the { } characters out of the JSON block and we simply have some field:value pairs.

Now uncomment lines 41-43 and reload the data so that we are running the first preceding load statement.

If you look at the preview for the Information_Flexible table you will see something interesting. We have multiple rows in the table for each of the original rows. As the comments indicated the SubField() function has done that. Each of our field:value pairs has been put into it's own row:

This is getting fun. So let's keep going. Now uncomment rows 31-34 and reload the data again.

As you look at the preview again for the Information_Flexible table you will notice something cool. The field:value JSON pairs, have been turned into structured Field and Value fields.

If we could created a table for each of the different Field values and store those Value values in it we would really be in business. Now I remember that's exactly what a Generic Load does.

Go ahead an uncomment lines 20-24 so that we can once again reload the data, and this time return to our starting point.

Summary

Like many things I have written in the past ... this solution isn't really all that complicated, but understanding how multiple Qlik Sense load transformations work together can be. Hopefully, you now feel confident that you have an example load script that you can use to flexibly parse out any nasty JSON Heterogeneous data that those varmints throw at you. Plus you realize how easy you can make it for yourself to simply comment out preceding load steps so that you can get a picture of what is occurring each and every step of the way. After all, not everyone documents their code so thoroughly. 🤠

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: