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

Thinking About JSON, part 4

This article is part of a series. See also…
•  Thinking About JSON, part 1
•  Thinking About JSON, part 2
•  Thinking About JSON, part 3

Demo Files

Make sure to download the correct version for your locale:

Introduction

Welcome back to the fourth installment in this ongoing series. It’s hard to believe it’s been four years since the last segment, but in the meantime some exciting developments have occurred at the intersection of FileMaker and JSON, including…

…and today we’re going to look at a custom function that can leverage JSONGetElementType to help make it easier for you to generate JSON.

Disclaimer

This is a work-in-progress that I am sharing freely with the developer community. It has been under development for the last month, and I will likely revise it in the future. It may contain bugs or not behave the way you think it should. Use at your own risk, and with a healthy dose of skepticism and common sense. And, needless to say, treat it as a starting point and modify it as you see fit.

Background

When JSONSetElement was introduced back in 2017…

…a number of developers, myself included, were intrigued by the inclusion of the JSONRaw type, and naively hoped that if we specified JSONRaw, FileMaker would magically figure the type out for us, and spare us the bother.

This wasn’t just laziness. If you’ve attempted to dynamically generate JSON (from the result of a SQL query, for example), you’ve likely found that correctly auto-detecting the type is a challenge — not an insurmountable challenge, but a challenge nonetheless, and one that developers have not been shy about grappling with, typically in the form of custom functions (the MBS plug-in has a function that will attempt to determine the type for you as well).

At any rate, we quickly learned that JSONRaw was not a magic wand, and that it was probably best avoided. As I wrote in part 2 of this series…

JSONGetElementType

Fast forward five years and with the release of FileMaker 19.5 we now have a new JSON function, JSONGetElementType

…which can help make automatic type detection more straight-forward than before, and should not be confused with the JSONGetElement function, which was introduced in FileMaker 16.

(Note: if you’re looking for a good general introduction to JSONGetElementType, check out these articles from Codence and Soliant.)

The reason I say “can help” is that JSONGetElementType evaluates elements within a block of JSON, or the block of JSON itself if you specify "" as the second argument. Assuming the function is pointed at a valid element, it will return a number from 1 to 6 to indicate the JSON type of the targeted element.

If, on the other hand, you point JSONGetElementType at something that is not a valid JSON element, it will return an error like this.

Which brings us to today’s demo files and custom function.

The Demo Files

The demo files contain more than 120 test cases. There may be some edge cases out there I haven’t thought of, and I would imagine there are opportunities to simplify the CF code.

If you decide to revise the CF, I would strongly suggest that you download both demos and make sure that all test cases survive your modifications. And if you come up with any test cases my CF doesn’t correctly handle, or have suggestions for improvement, I would appreciate you posting a comment here.

JSON.GetValueType

The goal for this custom function is to make it easy to auto-calculate the JSON type for a would-be element. Sounds great, hypothetically speaking, but, as our UK colleagues might say, “What a sticky wicket” this turns out to be in practice.

I had no idea there would be so many edge cases, and decisions to make (e.g., am I supporting scientific notation? Yes, including non-normalized, a.k.a. non-standard scientific notation).

The basic approach: quickly identify objects/arrays/booleans/nulls, then, if the element isn’t any of the preceding, determine whether it is JSONString or JSONNumber. The preamble to the CF states the purpose and assumptions.

Some observations…

This CF works equally well regardless of whether your default thousands and decimal separators are commas or dots. Each demo file has its system formats “baked in”, so to speak, so you can try both versions w/o having to change your computer’s system settings. If I had built the CF to only work with US-style settings (commas for thousands; dots for decimals) the code would have been less complex.

Because some postal (ZIP) codes in the northeastern portion of the U.S. begin with zeros, and because I couldn’t think of any defensible reason to put a leading zero on a number (unless it was a decimal), non-decimal numbers with leading zeros are interpreted as JSONString.

Why Bother?

It’s a valid question, and interestingly, while the CF was under development and I mentioned it to a number of colleagues, about half of them were curious what the use cases might be, while the other half responded with some variation on, “I need this now. Can I have a beta please?”

An example of where I’d like the CF to figure out the type for me might be auto-generating a JSON object from values in a record. Or an array of objects from a targeted set of records.

A related use case would be auto-generating JSON where, if a given value is present auto-determine the type, but if it’s empty encode it as JSONNull. This is a huge headache w/ numbers especially because, as the whole world knows by now, if you encode a value as JSONNumber but that value is empty, it will render as zero which often isn’t what you want.

With JSON.GetValueType, numbers will be encoded as JSONNumber but empty values will be encoded as JSONNull.

And, finally, here are some custom functions that rely on JSON.GetValueType to auto-assign the JSON type. (These and other JSON-related CFs will be featured in an upcoming article.)

JSON.ArrayFromList

JSON.ArrayFromSQL

JSON.ArrayOfObjectsFromSQL – without optional key names

JSON.ArrayOfObjectsFromSQL – with optional key names

Closing Thoughts

I owe a huge debt of gratitude to Steve Senft-Herrera for discussing this custom function and associated issues with me at length over the last month or so. All bugs, omissions, or sloppy thinking are of course my responsibility alone.

6 thoughts on “Thinking About JSON, part 4”

  1. Good CF, Kevin!

    One small gotcha: to check the FM version in a cross-local environment, you need to replace the dot with the decimal separator used in the file’s locale:

    If ( Substitute ( Get ( ApplicationVersion ) ; “.” ; Left ( 1/2 ; 1 ) ) * 10 < 195 ; "This CF requires application version 19.5 or later" ;

    I would also suggest entering values, calculating and displaying results in the same file via a one-to-one relationship, replacing the dot with a comma in the other file:

    Let (
    input = TestCasesDot::InputValue ;
    If ( Left ( JSONGetElement ( input ; "" ) ; 1 ) = "?" ;
    Substitute ( input ; [ "." ; Char(1) ] ; [ "," ; "." ] ; [ Char(1) ; "," ] ) ;
    input
    ))

    1. Hi Alex, just to clarify, your suggestions appear to apply to building a unified demo file that would work in any locale. Given that I intentionally built two different files, one for each locale, and configured them to always use the file’s stored settings, does your first suggestion still apply?

      1. In the second file, all you need is a copy of the CF and one calculation field that gets its values through a one-to-one relationship (FK=PK) from the first file, replacing dots with commas. The first file will display the results from the second file via the same one-to-one relationship (PK=FK). This should make the testing process easier.

Leave a Reply

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