18 Sep 2022 – these custom functions have been upgraded – see JSON Custom Functions for FM 19.5, part 2 for details.
Disclaimer: the technique shown in this article is provided on an as-is basis. Use with appropriate caution and at your own risk.
Demo file: JSON-Array-Custom-Functions.zip (requires FM 18 or later)
Have you ever noticed that FileMaker does not provide a JSON function to insert a new element into an existing array? For example, given this array (and bearing in mind that JSON uses a zero-based index)…
[ “A” , “B” , “C” , “D” ]
…JSONSetElement ( array ; 2 ; “XXX” ) will transform it to…
[ “A” , “B” , “XXX” , “D” ]
… but we have no native JSON function to “push” a new element into position 2 so that existing elements slide one position rightward to accommodate, like so:
[ “A” , “B” , “XXX” , “C” , “D” ]
JSON.InsertArrayElement is an attempt to address this shortcoming, and is an outgrowth of the custom function and issues we explored in my previous article, FM18: JSON.UpdateArrayElement.
The examples in the demo file are there for you to explore and change. As I said last time, long winded code explanations quickly become tedious, and I am happy to attempt to answer any questions posted in the comments section… but here are a few observations.
As implied by its name, JSON.InsertArrayElement is designed to work with arrays; you may discover some limited support for object transformation in there as well, but that’s not its primary aim.
Here’s an overview of what JSON.InsertArrayElement does under the hood:
- Determine the entry point in an existing array where the new array item will be inserted
- If the entry address is greater than the max existing key # in that location, then simply create the entry using JSONSetElement
- Otherwise split the source JSON into two halves, and insert the new entry between them
Like its predecessor, JSON.UpdateArrayElement, today’s CF is designed to work around the autogenerated array issue I mentioned last time.
One aspect of both this custom function and its predecessor, which I glossed over last time, is the auto-assignment of the JSON type. If you’ve ever created JSON in FileMaker you will be well-acquainted with the fourth argument of JSONSetElement (shown in red below).
JSONSetElement ( json ; keyOrIndexOrPath ; value ; type )
Meanwhile the custom functions only require three arguments,…
JSON.InsertArrayElement ( source ; address ; element )
…which correspond to the first three arguments of JSONSetElement. Why did I choose to make the type implicit rather than explicit? Mainly a) to see whether it was viable — it is, with a few edge cases which could prove to be exceptions — and b) because 99.9% of the time the type can be derived using common sense (for example, if the element starts with a “{” then the type is JSONObject).
Here are the edge cases. Elements consisting of…
- a number will be auto-typed as JSONNumber
- the word “true” or “false” will be auto-typed as JSONBoolean
- the word “null” will be auto-typed as JSONNull
…whereas you might, from time to time, want to encode them as JSONString instead. Of course if you have that need, you can always modify the custom functions so the auto-typing is more flexible, or remove the auto-typing altogether and make the type assignment explicit.
That’s it for today. Have fun and happy FileMaking.
I’m not sure about v18 as I’m not using that yet, but both v17 and v16 autosort JSON arrays. Which means it’s kinda pointless inserting data at a specific location as FM will autosort the JSON data? When would an insert be useful?
Hi Olger,
While it’s true that elements are autosorted (alphabetically by key name) in JSON objects, the same is not true for arrays. When creating an array, the addresses you specify via JSONSetElement determine the order of elements within that array. You don’t need to worry about array elements being rearranged due to auto-sorting.
As per the specification at json.org, the order of elements is meaningless within objects but meaningful within arrays.
An example of when this CF might be useful: when you have an existing array containing entries ordered from newest to oldest, and you want to ensure that ordering persists as you add new entries. With JSON.InsertArrayElement you insert each new entry at address 0 and all existing entries slide one position rightward to accommodate.
Regards,
Kevin
Is this demo file still available?
The link should be working now. If not, suggest you flush your cache, or try w/ a different browser.