This article is part of a series. See also…
• JSON Custom Functions for FM 19.5, part 1
• JSON Custom Functions for FM 19.5, part 2
Update 5 Nov 2024: JSON.Format2D has been updated to fix an obscure bug. See updated “rev1” demo file.
Demo file: json-custom-functions-part-3-rev1.zip
Note: some of these CFs make use of JSONGetElementType so require FM 19.5 or later.
Disclaimer: use at your own risk, these CFs may contain bugs, these CFs may not be performant when processing large amounts of data, always test carefully before deployment, etc., etc., etc.
Welcome back for the third installment of what has become a series on JSON custom functions. Today we have some new CFs, a couple v2 upgrades of CFs that were presented previously, and a couple examples where Steve Senft-Herrera’s JSONQuery provides a performant and convenient alternative.
Let’s start by taking a look at the upgraded CFs.
JSON.MergeArrays ( array1 ; array2 ; insertionPointForArray2 )
Purpose: combine two arrays into a single array.
Reason for upgrade: As Steven Blackwell might say, “Someone got a case of the clevers” when I created v1 of this CF. Here’s how it should have worked to begin with.
Ex. 1 — simple concatenation (leave third argument empty)
The great majority of the time you will simply want to append the contents of array 2 to the contents of array 1, so the above is the standard use case.
Ex. 2 — simple concatenation (with third argument specified)
You could instead do this, but it’s more work and the result is the same.
Ex. 3 — prepend array 2 to array 1
…which could also be accomplished by using the Ex. 1 approach but swapping the two array arguments.
Ex. 4 — insert array 2 into the middle of array 1
Ex. 5 — insert array 2 beyond the end of array 1
Ex. 6 — insert array 2 before the beginning of array 1
Okay that’s fine for merging two arrays, but what if you want to concatenate an arbitrary number of arrays? The first record in the demo file shows four arrays…
…but let’s assume the object could contain any number of arrays, taking this form.
{ key : array , key : array , key : array , etc. }
You might use the While function to iterate through the keys, invoking JSON.MergeArrays as many times as necessary…
…but if you have JSONQuery in your tool kit, you can achieve the same result with much less effort. (It’s included in today’s demo file if you’d like to give it a try.)
If you aren’t familiar with JSONQuery, or need a refresher… you can find the current version of JSONQuery with copious examples and documentation at https://github.com/steve-ssh/FMP_JSONQuery.
Two-part in-depth interview with JQ author Steve Senft-Herrera:
• JSONQuery, part 1
• JSONQuery, part 2
JSON.Format2D ( json )
Purpose: format a JSON array or object so that each top level element, regardless of complexity, is displayed horizontally.
Reason for upgrade: simplified code and extended scope to work with any JSON structure.
Ex. 1 — record 2 in the demo file represents an array of objects…
…which is transformed by the CF like so:
Ex. 2 — record 3 in the demo file represents an object with sub-objects…
…which is transformed by the CF like so:
Okay so much for the upgraded CFs. Let’s move on to the new ones.
JSON.StandardFieldObject ( tableOccurrence )
Purpose: create a correctly-typed JSON object of standard (editable non-global, non-container) fields.
Dependency: JSON.GetValueType (discussed in detail in JSON.GetValueType)
At first glance this may appear to be the same as JSON.ObjectFromRecord, which we saw in part 2, but that’s not the case. Both CFs auto-generate a JSON object based on the current record but this CF works with “standard” editable fields only, i.e., ignores calculated, summary, global and container fields.
For example, given these field definitions, only the highlighted ones should qualify for inclusion.
Let’s point the CF at record 1 (note that the repeater field is not displayed on the layout, and is empty)…
…and sure enough, the non-standard fields are ignored.
Note 1: elements are properly encoded, e.g., 4444 not “4444”, and nulls for the empty repetitions.
Note 2: the tableOccurrence argument should be explicitly specified — here’s why: if you use Get ( LayoutTableName ), a calculated field using this CF in a related table will evaluate as if it were defined in the parent table.
Note 3: I decided to auto-format the output as part of the CF. If you prefer unformatted output, you can use JSON.Deflate (introduced in part 2), or JSONGetElement with an empty second argument.
(Or, needless to say, you can modify the CF as you see fit.)
JSON.ObjectFromArrayOfObjects ( sourceArray ; keyName )
Purpose: convert an array of objects to a JSON object
In record 4 we have a large-ish array of objects, each object containing demographic country data (courtesy of restcountries.com).
We can transform that array into an object with key/value pairs where each key is a country name like so:
Internally the CF is using a While loop and it takes several seconds to iterate across all 250 objects.
An alternative would be to use the “transformation map” component of JSONQuery, as per example 155 in the above-referenced file.
In this case we can use the map structure as presented above, replacing “email” with “name” as the “SOURCE” value.
Why go to this extra work if the result is the same either way? Performance, that’s why. The result renders in less than a second. Here’s the code if you’d like to test it for yourself.
Let ( MAP =
"{
\"TEMPLATE\": \".SELF.\",
\"MAP\": [
{ \"SOURCE\": \"name\", \"OUTPUT\": \".KEY.\" }
]
}" ;
JSONQuery ( sandbox::json ; "" ; "MATCH_ALL" ; "" ; "" ; MAP )
)
Incidentally, I heard a rumor that in a future version of JSONQuery you’ll be able to simplify the above like so:
26 Nov 2024: And that rumor turned out to be true — see JSONQuery v2 Conversation with Steve Senft-Herrera, part 1.
JSON.KeysToVars and JSON.KeysToVarsWithReps
Purpose: generate $variables from the key/value pairs within a JSON object.
To see this in action, go to record 1, open your Data Viewer to the Current tab, and run this script.
Ex. 1 — variables without repetitions
When you run the script you will be given an opportunity to optionally specify a repetition number for the variables, and note that 0 and negative integers are valid rep #s for variables (unlike field rep #s which must be positive integers).
Ex. 2 — variables with a positive rep #
Ex. 3 — variables with a negative rep #
Finally, be aware that this CF will fail if any of the keys in the object contain a forbidden character or word as per the red box below.
(For more on this, see Set Variable By Name Revisited.)
So you can dynamically instantiate variables from record 6, where all the names are legal…
…but if you try to do the same for record 5, you will instead see this…
…due to “illegal” characters in some of the country names, such as parentheses and commas, e.g,
Bolivia (Plurinational State of)
Bonaire, Sint Eustatius and Saba
To be clear, those names are perfectly legal as far as JSON is concerned, but FileMaker variable names are less forgiving.