JSON, Level: Intermediate, Version: FM 19.5 or later

JSON Custom Functions for FM, part 3

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

Demo file: json-custom-functions-part-3.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… here is the current version of JSONQuery with copious examples and documentation:  CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip

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 (which was discussed in detail in Thinking About JSON, part 4)

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:

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.

2 thoughts on “JSON Custom Functions for FM, part 3”

  1. Kevin,

    Your JSON.ObjectFromArrayOfObjects() function is so slow because you are using JSON functions and the JSONQuery() function is using Text functions. You can easily optimize your function to be 3 times faster that the JSONQuery() function:

    While ( [
    objectList = JSONListValues ( sourceArray ; “” ) ;
    x = 1 ;
    object = GetValue ( objectList ; x ) ;
    result = “”
    ] ;
    object ≠ “” ;
    name = JSONGetElement ( object ; keyName ) ;
    result = List ( result ; JSONSetElement ( “{}” ; name ; object ; JSONObject ) ) ;
    x = x + 1 ;
    object = GetValue ( objectList ; x )
    ] ;
    JSONFormatElements ( Substitute ( result ; “}¶{” ; “,” ) )

    BTW, I noticed that when the JSONQuery() function is applied to record 6 in your test file, it returns an object of a different length. Your function returns 347874 characters, while the JSONQuery() function returns 347739 characters.

    1. Hi Alex,

      First off, that is a clever, lightning fast approach and I appreciate you taking the time to author it and share it here.

      I also appreciate your observation that JSONQuery and my JSON.ObjectFromArrayOfObjects under certain circumstances will return different length results, and I’d like to respond to that.

      Since my CF and your code were both designed to take an array of objects as input, I’m going to use example 4 in this article’s demo, and use the country “name” as the keyName.

      At first I assumed that any discrepancy would be due to differences in formatting of the result, but that hypothesis was easily disproved by ensuring the output was unformatted like so…

      JSONGetElement ( result ; "" )

      …in which case your code and my CF produced identical length output, while the JSONQuery output was 8 characters less.

      The culprit turns out to be the “Virgin Islands (U.S.)” entry. JSONQuery correctly renders the country name as…

      "Virgin Islands (U.S.)" : { ...

      …whereas both your code and my CF render it as…

      "Virgin Islands (U" :
      "S" :
      { ...

      In other words, neither of us anticipated the possibility of, or coded for the possibility of, a dot in a country name.

      In this particular instance, assuming one is using FM 19.3 or later, and for the benefit of readers who may not already know this, one can take preventive measures to ensure dots in key names are escaped by wrapping the name in braces and single quotes like so.


      Specifically this portion of your code would change from…

      List ( result ; JSONSetElement ( "{}" ; name ; object ; JSONObject ) )


      List ( result ; JSONSetElement ( "{}" ; "['" & name & "']" ; object ; JSONObject ) )

      …and I would need to make similar change to my CF, to ensure key names are properly escaped if they contain dots.

      I’ll have more to say on this subject in an upcoming article.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.