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:

• json.getvaluetype – decimal dot, v5
  (decimal separator is a dot; thousands separator is a comma)
• json.getvaluetype – decimal comma, v5
  (decimal separator is a comma; thousands separator is a dot)

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…

• The Execute FileMaker Data API script step
• Steve Senft-Herrera’s amazing JSONQuery custom function
  • JSONQuery, part 1
  • JSONQuery, part 2
  • YouTube presentation on JSONQuery
• The new-in-19.5 JSONGetElementType function

…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.