Introducing JSON Filter, Map, Reduce.
Now that FileMaker has given us a practical and more simplified way to loop in a calculation with the While() function, the doors have opened to other ways to look at looping.While() Is Great!
While() is such a great tool and if you are new to the While() function I would recommend taking a look at this great post by Carter Brooks, “We’ve been waiting for While()… for a while“. While() does a great job at simplifying what would previously need to be done with a recursive function.Looping JSON
It turns out that often when we use While() we are taking a set of data like an Array and looping over each item. In the age where FileMaker acts as a tool for integrating systems together, JSON is often the protocol used to communicate between systems. Also JSON has become more prevalent in native FileMaker calculations and scripts.Why Is It Complicated?
Most often when you are creating a loop using a script loop or While() there is considerable setup.- You have your data set that you need to find the length of.
- You set a counter.
- You set a condition that when the counter reaches the length of the data set it will exit.
- You set up logic to happen with each iteration.
Context
Reducing the overhead of a full While() function does require some thought. We are going to remove some of the work and stop focusing on the whole data set. Now we are going to focus on one element. Doing this will require having an abstracted context to work with. Let’s use this JSON Array and Object as reference and look at the abstracted context.$array
[
{
"age" : 30,
"id" : 1,
"name" : "Tim",
"status" : "active"
},
{
"age" : 22,
"id" : 2,
"name" : "Jen",
"status" : "active"
},
{
"age" : 80,
"id" : 3,
"name" : "Bill",
"status" : "pending"
},
{
"age" : 50,
"id" : 4,
"name" : "Beth",
"status" : "inactive"
},
{
"age" : 30,
"id" : 5,
"name" : "Bob",
"status" : "active"
}
]
$object
{
"apples" : 20,
"bananas" : 23,
"blueberries" : 25,
"grapes" : 20,
"oranges" : 25
}
Here are the context arguments you get to work with. In the first iteration things would look like this.@value = This is the current element in context. For the Array the first item would be
{
"age" : 30,
"id" : 1,
"name" : "Tim",
"status" : "active"
}
For the Object the first item would be 20 – the number of apples.
@index = This is the index used to get the first item. This always starts with 0 and increments each iteration. @key = This is the key used to extract the @value.
For the Array this is the same as @index
For the Object in the example. “apples”
@data = The whole data set passed to the function.
This is only used if you want to make reference to other location in the data.
@result = The result of the last operation. This is only used in the Reduce function in order to pass an accumulator from one element to the next.
While() At The Core
Using these abstracted arguments we can focus on one element at a time. We also don’t need to think about the size of our data. One note however is, this really is using the While function to accomplish this. It is still subject to While iteration limitations. So if your data set is large, you will still need to adjust the recursion limit.Expression As String
One last thing to note. Because we are going to be passing an expression it needs to be a string. This is a pain but the only way to get an expression into a function as far as I know. So to avoid escaping quotes all over the place I have also applied a Substitute function to all single quotes turning them into regular ones. If you need a single quote in there for some reason those need to be escaped by \’. I figured it was a nice trade 🙂Scope And Performance With $var
If you are curious and dig into the custom functions, you will find that there is one variable that may look a bit peculiar. This is $fData[~vid] This variable is a script level variable. The purpose is to improve performance by having one static variable that remains consistent during all the looping. In this case it is used to store the original data so you can refer to it at any point in the loop. The reason it is stored in a $ script variable is so when you run the Evaluate function it can take the variable as a string. Because it is in the larger scope it can be referenced and evaluated. Not having to store separate instances of the variable improves the speed greatly when referencing the data object. The ~vid is a UUIDNumber that makes the variable unique to the process of running the function. This eliminates the problem where you could be running a script with a variable named $fData that then gets overwritten. This also provides safety when nesting these custom functions together.Filter
The common use of filter is as you can imagine to filter items from a collection. That collection can be an object or an array. All you need to do is create an expression that resolves to a Boolean value (True or False). Let’s use the Array example above to filter for people that are over 35yr old. JSON_Filter (
$array ;
"JSONGetElement(@value : 'age' ) > 35"
)
Returns…
[
{
"age" : 80,
"id" : 3,
"name" : "Bill",
"status" : "pending"
},
{
"age" : 50,
"id" : 4,
"name" : "Beth",
"status" : "inactive"
}
]
Map
The common use of map is to alter each element as it traverses the data. So instead of using a boolean result we are going to create the element we want at each index. This is great for examples like when you get data from a web service and it isn’t in the format you like.Using the example Array at the top I am going to change it to match what I would like it to be. I am going to 1 change status to a Boolean. I only care if the user is active or not. I am going to also change the name key to userName and finally I am going to drop the id.
JSON_Map ( $array ;
"JSONSetElement( '' ;
['status' ; JSONGetElement ( @value ; 'status' ) = 'active' ; JSONBoolean ];
['userName' ; JSONGetElement ( @value ; 'name' ) ; JSONString ];
['age' ; JSONGetElement ( @value ; 'age' ) ; JSONNumber ]
)"
)
Returns…
[
{
"age" : 30,
"status" : true,
"userName" : "Tim"
},
{
"age" : 22,
"status" : true,
"userName" : "Jen"
},
{
"age" : 80,
"status" : false,
"userName" : "Bill"
},
{
"age" : 50,
"status" : false,
"userName" : "Beth"
},
{
"age" : 30,
"status" : true,
"userName" : "Bob"
}
]
Reduce
Reduce is special. It doesn’t need to even return the same type as the data. It’s great for summing things. Here is a simple example to get the amount of fruit from the Object example:JSON_Reduce (
$object ;
"@result + @value"
; 0 )
Returns…
113That example is not very creative so here is another I like a little more! I am going to formulate a sentence from the keys.
JSON_Reduce (
$array ;
"@result & Case (
@index = 0 ; '' ;
@index = ValueCount ( JSONListKeys ( @data ; '' ) )-1 ; ' and ' ; ', ') & @key "
; "I enjoy " ) & "!"
Returns…
I enjoy apples, bananas, blueberries, grapes and oranges!
Give It A Go!
I hope you can see how Filter, Map and Reduce can really become handy tools in your FileMaker development. I would love for you to try the demo file for more examples and use it as a handy place to test the functions. JSON Filter Map Reduce FileMaker Demo [357 KB download]Thanks!