The FileMaker Filtering JSON function

The FileMaker JSON functions we have in FileMaker 16 have changed the development game for many folks. JSONSetElement, JSONGetElmeent, JSONDeleteElement, JSONListValues, JSONListKeys are powerful functions that let us collect data into an object, to parse data from an object, or to edit the object itself. We can even format a JSON object using JSONFormatElements. Since JSON’s purpose is to exchange data, it seems these functions are good enough. We at Geist Interactive have combined these with other FileMaker functions to produce some custom functions (in JSONAdditions.fmp12)  that are useful in many circumstances. Our custom functions actually manipulate the JSON in some way, doing just a bit more than the native functions. In this post, we’ll take a look at a FileMaker filtering JSON function: JSON.FilterByExpression.

You can follow along with this download here.

Simple & straightforward

This is a simple custom function. Its purpose is to return the elements of an array that satisfies an expression. Here’s the syntax:

JSON.FilterByExpression( array ; expression)

You simply pass in it an array and an expression (in quotes), and this custom function returns those elements in the array that satisfy the expression.

The expression is the key. You write an expression that can be used to check a value of a key in each element of the array. Those elements that have have values that meet satisfy the expression are returned.

Let’s apply this custom function to an example, starting with the use case.

Use Case: Given an object returned from the client’s shopping cart website api, chart any number of years’ unit sales. Give the user the ability to choose the start and end year, and display the units sold for that year range on a chart.

Here’s the “sales” array returned from the api and placed into the field: shoppingCart::JSON

{
"sales" : [
{
"year" : 2011,
"units" : 43
},
{
"year" : 2012,
"units" : 22
},
{
"year" : 2013,
"units" : 100
},
{
"year" : 2014,
"units" : 2
},
{
"year" : 2015,
"units" : 12
}
]
}

JSON.FilterByExpression can easily parse through this array and return those elements that are within the boundary years.

JSON.FilterByExpression ( 
JSONGetElement (shoppingCart::JSON ; "sales")  ;  
"JSONGetElement ($item ; \"year\") ≥ 2012 and JSONGetElement ($item ; \"year\" ) ≤ 2015 
)

Will return

[{
"year" : 2012,
"units" : 22
},
{
"year" : 2013,
"units" : 100
},
{
"year" : 2014,
"units" : 2
},
{
"year" : 2015,
"units" : 1
}
]

The FileMaker filtering JSON custom function is recursive. It will loop over all the elements in an array and construct a new array of those elements that agree with the expression

Express(ion) yourself

The goal of my custom function is to return those elements that have years between 2012 and 2015 inclusively. So that’s how I wrote the expression:

"JSONGetElement ($item ; \"year\") ≥ 2012 and JSONGetElement ($item ; \"year\" ) ≤ 2015"

Notice a few things:

1. The FileMaker expression must be quoted.
2. The expression will be applied to each element in the array
3. The variable $item will contain current item in the array. (Kind of like the current record when looping records.)
4. I’m using “JSONGetElement()” function to pull the ‘year’ value.
5. Since the expression is quoted, I’m escaping the ‘year’ key.
6. I’ve got two parts to my expression in this example. But any expression and any combination of expressions can work.

Here’s what my expression is saying:

For this object in $item, check to see if the value in the key “year” is greater than or equal to 2012 and less than or equal to 2015.

As this FileMaker filtering JSON custom function loops through all the elements in the array, it checks: Does the value in “year” satisfy this expression? If so, add the element to an array.

Finishing the use case

Once I’ve gotten back the array elements that satisfy the expression, I can use another custom function: JSON.GetValuesAtPath (array ; path) to return the “units” values. I’ve done this before in another charting exercise. We will look at this function in a later post.

And thus, the chart is born. (Well, there’s a little more to the chart, but you can view that in the sample file).

The JSON Structure

We got an email of someone struggling with this custom function. It returned a “?”. After examining the person’s data structure, I realized what he gave us wouldn’t work. It looked like this:

{"2012": [
{
"value": "22"
}
],
"2013": [
{
"value": "100"
}
]
}

The structure, besides being valid though unnecessarily complex, doesn’t contain an array of objects that the custom function JSON.FilterByExpression can loop through. Here the year is the key to a one-element array of one JSON object containing one key/value pair. This is an object. It is not an array, so our custom functions wouldn’t work.

By the way, this makes me wonder: what is the ‘proper’ structure for JSON and data. I will have to explore that.

Our custom functions work with arrays. Specifically, the custom functions loop through the elements of the array and returns a new array or list.

But that’s okay. Our custom functions work with VERY COMMON JSON data structures. Often an api will return arrays within the entire object, just as we see in the working example above. If we can drill down and grab an array from the object, we can use these custom functions.

New tool: FileMaker Filtering JSON

It is wonderful that we now can natively work with FileMaker filtering JSON to gather records into an object, to parse api results, and to manipulate and configure the JSON. We’ve just added a few more we find useful.

Give our JSONAdditions file a try. There’s some useful custom functions that will further your ability to not only generate data into a JSON object, but also to filter, merge and otherwise manipulate an existing object.

If you do adopt these into your own solution, I recommend simply copying / pasting all the custom functions into your file; many of these need other custom functions.

Go forth, now and expand your FileMaker JSON toolset.

If you didn’t download it before, take a look at the sample file. It uses the chart object in FileMaker.