Parsing: JSON Array (Homogeneous Objects)

Dalton_Ruer — Tue, 04 Nov 2025 17:37:45 GMT

The image depicts what is called a JSON Array and to be precise its a JSON Array of Homogeneous Objects. I'm sure you are no greenhorn at this point in my series on parsing JSON. If I said tell me the name who is the owner of the third saloon in our town, I'm sure you would immediately reply "Miss Ada."

This post is going to focus on how you came to your answer. Guess what function Qlik Sense provides that will allow you to parse that answer out of this unstructured textual array just like you were able to do with your human mind?

JsonGet()

If you guessed it was the same JsonGet() function covered in the previous posts on Parsing: Flat JSON - Field Value Pairs and Parsing: Nested JSON Objects you are 100% correct.

The how is where it gets interesting. In the post Parsing: Nested JSON Objects, I brought up the concept that we needed to qualify the path to our field:value pair and we worked through how accessing the name field for an entity called sheriff was accomplished by doing this:

I told you that the notation was essentially /entity/field. But you somehow managed to astound me with your ability to parse the opening image and provide the answer to my question about the third saloon. You were able to answer because you automatically, in your mind, altered that notation to be something like /entity/INDEX/field.

The essential part of this post is the fact that the INDEX value starts at an offset of 0. Meaning the first value has an index of 0.

Assume that the JSON structure from the image at the start of the post is inside a field called Array that is in a table called JSON_Array. The following code would pull out values from the array. Notice that we pull the name, owner and capacity field values for the first saloon. We only pull the name for the second saloon. The last line simply pulls the entire JSON block, the "row" itself for the final saloon in our array. Maybe it will deal with parsing it later. It's entirely possible that I purposely did that in this basic post to prepare for a much much deeper post in the future where that is in fact what I do. I pull out the block and deal with it in a different part of the code.

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 "3 - JSON Array: Homogeneous 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. In the other articles it was easy for me to simply create an inline table that contained that JSON Block. But for an array we have a special issue to deal with ... those brackets the define the beginning and end of the array "[" "]"

Those 2 characters are special and define the beginning and end of our inline statement and thus they cause a problem. Notice that I've simply duplicated the characters [[ and ]] in my line of code, so that the inline table reads it in, and then I do a preceding load to pull the duplicate brackets right back out.

Preview

Before you worry about making any changes, simply load the script as is and go to the Preview window and see the JSON_Array table. You will then see that our Array field is formatted as needed to be a proper JSON Array.

Iterative

When I began the article I asked you to focus on the third saloon. Then I showed you the code needed to parse out and get the same response from the code that you were able to do mentally. 3 entries fit my example case for this post because ... I needed to parse out the array 3 different ways. I wanted all values for a "row", only 1 value for a "row" and just the JSON for the final "row."

But I don't want to leave you hanging there. Some rascally gunslinger is bound to head into your town with 5 rows. Or 10 rows. Or 20 rows. The last thing I want you to do is sit there and hand code a hard coded limit and then tell the good folks in your town that as Data Sheriff you will happily give them insights from the first 20 values. They just might run you out of town.

Never fear my friend, Qlik Sense provides a fantastic function call that will actually iterate through as many rows as needed. The function is called IterNo() and as the help makes really clear, the only useful purpose for the IterNo() function is when used with a While condition. In other words ... you need a way to ensure we end the recursion.

So here is how we can utilize it to parse our JSON Array of Homogeneous Objects. Rather than just reading the resident JSON_Array like we did above when hardcoding the parsing, we simply take advantage of the IterNo() function and then combine it with another Qlik Sense function called IsJson().

Resident JSON_Array
WHILE IsJson(JsonGet(Array, '/saloons/' & (IterNo()-1)));

The IterNo() function will start at 1 and then keep iterating, until you tell it to stop. Easy breasy.

The IsJson() function will say "Yep you have properly a properly formatted JSON structure" or "Sorry partner you drew a bad hand."

Thus ... as the IterNo() function iterates through we will be handling the following:

WHILE IsJson(JsonGet(Array, '/saloons/0')));
WHILE IsJson(JsonGet(Array, '/saloons/1')));
WHILE IsJson(JsonGet(Array, '/saloons/2')));
WHILE IsJson(JsonGet(Array, '/saloons/3')));

The first 3 iterations will succeed, but the 4'th iteration will return nothing and thus end our while loop. As you will see in the script in the WildDataFrontier application, we can then simply refer to the iteration number (will remain constant for the row being processed) and pull out our values for every row that is returned in the array. Whether it be 1, 10, 20, 100 or more.

Thus when we preview the flat/hardcoded method the results are usable

But when we preview this iterative approach the results are usable today, tomorrow and next year. So, you can remain as the Data Sheriff.

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.