Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The image above depicts a JSON Version 2 structured array. Rather than repeating the column/field names over and over in pairs with the data, they present the fields and the data separately.
JsonGet()
When I first encountered this structure, I asked myself "How in the world am I supposed to read the data into a structured placeholder?" I couldn't find any type of JsonGet example where it said "read this data array and just use your esp to know what the field names are supposed to be.
With any problem like this where the answer doesn't seem obvious, my recommendation is to just get started with what you can achieve ... so I did. I started by separating out the information that I could with the basic JsonGet field/value pair syntax.
After that I had the information that would be needed, broken up into digestible pieces.
Field Names
Then I started with the field names. Notice that it is simply a JSON Array of Homogenous Objects and we already know how to deal with those by sprinkling on a little of the Qlik iteration magic:
Voila ... we have a table of the FieldValues so we know the names of each of our columns.
Visualize the Solution
That was the easy part. I still wasn't sure how in the world I create a table where those were the columns and the data values would be the ... well ... the data values. So, I tried to visualize what I was looking for by adding the following as comments in the code itself as a reminder/muse.
SHERIFF_BADGE, TOTAL_BULLETS_USED
1307919, 3221
1617792, 2690
Then a crazy notion hit me ... that looks exactly like what I would do for an INLINE table.
EXACTLY LIKE AN INLINE TABLE.
So .... why not build it as an inline table?
What I wanted was something like this ...
vCortexHeader
Obviously I needed to build the Header variable first. A little housekeeping first to set some variables then I just needed to loop through however many columns/fields the structured array might have. In my real case, the number of fields was more than 2, but I shortened them to help you track the solution. The logic works regardless of the number. If it is the first field, then set the vCortexHeader to the name of the first field. If it is not the first field, then update the vCortexHeader so it equals the previous value, and add a comma, then add the name of the next field.
Voila ... the header for my soon to be inline table of values.
vDataValues
At the beginning of this post I showed what the ResultData looked like as part of the overall Response table that was constructed using the simple JsonGet function. I've expanded here so you can focus on just it ... notice that other than some extraneous characters it is literally in the format we need for an INLINE table.
If you take out the open/close square brackets "[" "]" and the double quotes ... the data is right there. We already know from previous posts how to use the JsonGet function to get row 1 (which is 0 offset) and get row x ....
So, then it is just a matter of removing those double quotes and square brackets:
Putting it all together we pretty much do what we did with the field names, except this time we added carriage return line feed characters before rows 2 through x:
And we ended up a vDataValues variable that looks like this:
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 "7 - Structured Array" is the one you want to have at the top of your script. }
Edit line 49 that had 2 data values and a third data value , ("7777777", "777") and modify the numRows value from 2 to 3 like this:
'{"table": {"result_set": { "data": (("1307919","3221"), ("1617792","2690"), ("7777777", "777")), "resultSetMetaData": {"format": "jsonv2","numRows": 3, "rowType": ({"length": 0,"name": "SHERIFF_BADGE","nullable": true,"precision": 38,"scale": 0,"type": "fixed"},{"length": 0, "name": "TOTAL_BULLETS_USED", "nullable": false,"precision": 18, "scale": 0, "type": "fixed"} ) }, "title": "Top 2 Sheriffs for number of bullets used"} }}'
Since this solution involved a lot of variables, feel free to use the Debug mode and set breakpoints where you want them so that you can see the values as they are set. Or simply just reload the data after your changes and check out the values in each of the tables created so you can confirm what was done.
