Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner

Dalton_Ruer · Nov 4, 2025 7:58:15 AM

As you look at the image above you will notice multiple JSON objects. In previous articles we've dealt with single JSON objects. Now we need to deal with the fact that the file has multiple rows, or we called an API that returns as a streaming set of values, rather than just a single response.

Like previous posts these JSON objects have field:value pairs that we can certainly parse out, but each record seems to be different than the previous. There is a type field, but the value for type seems to indicate a different entity type all together.

SQL Like

In previous posts I tried to help you relate the unstructured JSON block to it's SQL counterpart.

When I presented Parsing: Flat JSON - Field Value Pairs we discussed how a JSON structure was similar to a single row/record of a table.

When I presented Parsing: Nested JSON Objects we discussed how a nested JSON structure was similar to a select clause that joined data from multiple tables.

When I presented Parsing: JSON Array (Homogeneous Objects) we talked about how the array structure was similar to returning multiple rows from a table, rather than a single record.

In SQL terms this would be like reading a table and if the value said report, then you would join to a table that stored values for a report. If the value said wanted_poster, then you would join to a table that stored values for a wanted_poster. If the value said telegraph, then you would join to a table that stored values for a telegraph.

Wait, that's not really how SQL works is it? You don't have columns that might be a foreign key to any of 3 different (or more) other tables. Complete random thought here ... What if this kind of flexibility is exactly what makes JSON a great data exchange format?

Enough random brainstorming ... let's get back to parsing out this data.

JsonGet()

Qlik Sense provides a very easy to understand function called JsonGet that we will use to get the values for the fields contained in this homogeneous JSON data. It wouldn't matter to you if I asked you to tell me the text contained in the report, or if I asked you for the bounty amount for Billy the Byte's wanted_poster, or if I asked you to tell me the message contained in the telegraph. You would simply find the fields and tell me the value.

If you are anything like me I'm sure you worried/fretted/shook in your boots that the function would return an error if a value you asked for didn't exist. But stand tall data sheriff, because it will simply return null if you ask for a field in the JSON structure that doesn't exist.

Thus the easiest way for you to read all of this homogeneous data is simply call the JsonGet function for all of the field types from any of the entity types. It's that simple:

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 "4 - Mixed JSON: Fixed Values" is the one you want to have at the top of your script for this post. }

Each section of the application begins with preparing the JSON object(s) we will be parsing. Previous posts loaded a single JSON block, but in this case we have a simple inline table with multiple rows. To help you more deeply understand this set of data imagine that you are reading data from a source with multiple rows, or an API keeps responding with results and each row you read you use the RowNo() function to create an ID for that row and stores it in a field called RowNumber.

Preview

Before making any changes to the data be sure to load the data for this section and check out the preview. I want to ensure that you are confident in the fact that jail cells aren't going to blow up when you call the JsonGet() function for a field:value pair and that row of data doesn't exist.

Now go ahead and add a new row to the inline table:

4, '{ "type": "homestead", "coordinates": "Some Where", "amount" : 7777 }'

Before actually adding any new JsonGet() function calls in the script just go ahead and load the data so that you can see that it has no problem reading your new row and will display the homestead type in the preview window for you. Now go ahead and add the 2 lines of code you need to handle the 2 new fields for the homestead type: coordinates and amount.

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: