Demo file: traverse-arrays-with-mod-and-floor.zip
Introduction
Today I want to talk about a technique that can come in handy if you need to walk a block of contiguous cells within a two-dimensional (2-D) JSON array for either reading and/or writing purposes.
[ [ n, n, n, n, n, n, n, n, n, n, n, n, n, n, n ], [ n, n, n, n, n, n, n, n, n, n, n, n, n, n, n ], [ n, n, n, n, n, n, n, n, n, n, n, n, n, n, n ], [ n, n, n, n, n, n, n, n, n, n, n, n, n, n, n ] ]
The standard approach would be to employ a While within a While, the outer to walk the columns and the inner to walk the rows (or vice-versa). And that’s a very valid way to go about it, but I was wondering whether a 2-dimensional array of arbitrary height and width could be traversed via a single While call. Well it turns out that yes, it is possible, and we’re going to look at an approach that can accomplish this with help from the Mod() and Floor() functions.
Let’s say we have a 4 row × 15 column array, for a total of 60 “cells” that we want to iterate through. Since JSON uses zero based indexing, we can consider the rows to be numbered 0 – 3 and the columns to be numbered 0 – 14 like so…
…and we’ve been tasked with assigning the letters A through Z, one letter per cell, column-wise from left to right, starting with column [0]. Since there are only 26 letters in the English alphabet, after Z has been assigned start with A again, and keep iterating through the sequence until all 60 cells have been populated (and while we’re at it, let’s go with lower-case to make it easier on the eyes).
Conceptually, it looks like this…
…and as JSON, like this.
To be clear, we are interested in a general-case approach that will work with any number of rows and columns, and accommodate a payload with any number of items. For example, if we go with 9,8,7,6,5 instead of A-Z, this should be the result.
Today’s Approach
There are various ways this little challenge can be solved, but I like this approach for its relative compactness and the opportunity to use Mod and Floor. In a nutshell…
- Mod — returns just the remainder (if any) from a division operation, and comes in handy when you need to perform modular arithmetic. For example, if it’s currently 11 AM and you want to know what time a 12-hour clock will display 15 hours from now, Mod ( 11 + 15 ; 12 ) will provide the answer (2). If there is no remainder, then Mod will return 0.
- Floor — rounds a given number down to the nearest whole integer, for example, Floor ( 11 / 5 ) = 2.
At any rate, the basic idea in what we’re about to see is to
1) come up with a maximum value via this simple formula…
rows × columns - 1
2) increment a counter from 0 up to that number, and
3) have the code figure out the corresponding array address during each iteration.
Specifically as the counter increments from 0 to 59, on each iteration…
- Mod will generate the row address by translating the counter value into 0, 1, 2, or 3.
- Floor will generate the column address by translating the counter value into 0, 1, 2 … 14.
Conceptually the addressing scheme looks like this…
…but of course JSON doesn’t care about the conceptual model. If we want to target, say, cell 18 above, it will need to be translated into an address JSON understands — in this case [2][4].
The [2] corresponds to the row, and is generated via Mod ( 18 ; 4 ) where 4 is the total number of rows. Explanation: divide 18 by 4 and we get a remainder of 2.
The [4] corresponds to the column, and is the result of Floor ( 18 / 4 ). again, where 4 is the total number of rows. Explanation: 18/4 = 4.5, but Floor rounds down to the nearest whole integer so we get 4.
Example 1: A-Z by column and row
Note: In all these examples I’m using a custom function, JSON.Format2D, to ensure a spreadsheet-like appearance and to make it very clear that this is a 2-dimensional array. Otherwise the formatted result would look like this, completely obscuring the column and row structure…
…or we could skip formatting, which actually isn’t too bad as long as we don’t excessively widen the data viewer.
Example 2: A-Z by row and column
What if we want to iterate row by row, instead of column by column? Change three lines as per the highlighted section.
Example 3: changing the payload
Returning to column orientation, what if we want to use 9,8,7,6,5 instead of A-Z? Easily done.
Example 4: transforming a block within a larger array
What if we want to place the above block into a larger array, and at a starting address other than [0][0]? No problem. Let’s assume this is our larger array, and we want our 4 × 15 block to overwrite the highlighted section…
…with the 9,8,7,6,5 from the previous example.
Example 5: mixed payload (carelessly processed)
This example introduces a complication, because we have a mixed-type payload. We could take the easy way out and type everything as JSONString, but ideally we’d like each value to be correctly typed. Well it turns out that we’ve been dodging a bullet that I’ve intentionally glossed over till now. Do you see any problems with the highlighted entries? (Do they accurately reflect their initial appearance in itemlist?)
Explanation: the "" in red is the culprit. The fourth argument in JSONSetElement is where the type is assigned, and if we leave it empty, it defaults to 0 (zero), i.e., JSONRaw, which says to JSONSetElement “you figure it out”. It’s a double-edged sword, because a lot of times JSONRaw guesses correctly, but as we can see here, not always.
Note 1: I knew that it would be safe to use it in examples 1-4, where the values were unambiguously numbers or text. I also knew it would get confused by this example which is why I’m calling it out.
Note 2: watch for a more in-depth exploration of JSONRaw in an upcoming article.
Example 6: mixed payload (responsibly processed)
There are various ways one can remedy this, and here we will do so via a custom function to auto-detect the type — I’ve added a few more entries to itemlist to help illustrate that the values are being correctly typed.
Closing Thoughts
For more about auto-typing (a lot more, actually) see this article: JSON.GetValueType, and to learn more about the pitfalls of treating numbers as text, either intentionally or inadvertently, check out When 2 is Greater than 10.