Thinking About JSON, part 2

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

This is a follow up to Thinking About JSON, part 1. Last time we were primarily concerned with learning about JSON paths and structures, and reading JSON. This time around we’re going to look at creating and manipulating JSON.

Demo file: winery-json.zip

(If the above screen shot looks familiar you have a good memory, because today’s demo is based on the one that accompanied this article: Summary List Fields in FM 13.)

To briefly recap, JSON is built on two structures…

• Objects: surrounded by {} and consisting of comma-separated key:value pairs
  Simple example:  { “product” : “FileMaker Pro” , “version” : 17 }
• Arrays: surrounded by [] and consisting of comma-separated values
  Simple example:  [ 2 , 4 , 6 ]

…and where things get interesting is that the “values” in either of the above structures can themselves be JSON (i.e., an object or an array). This defining feature of JSON, whereby a JSON structure can, and frequently does, contain embedded smaller JSON structures, was explored in detail in part 1, and we will see some examples of this today as well.

FileMaker has six built-in JSON functions. Last time we explored these four:

• JSONFormatElements
• JSONListKeys
• JSONListValues
• JSONGetElement

This time around we’ll be looking at the remaining two:

• JSONSetElement
• JSONDeleteElement

JSON is created and updated via JSONSetElement, which takes these arguments:

JSONSetElement ( json ; keyOrIndexOrPath ; value ; type )

Simple Example #1

Okay, let’s create some JSON by running Simple Example #1 in the demo file.

The script initializes $$simpleJSON as a pair of braces (i.e., creates an empty JSON object)…

…and sets the first key/value pair, with JSONString as the type (since the value is text)…

…at which point our JSON looks like this:

Next we add another key/value pair, with JSONNumber as the type (since the value is a number)…

…so now our JSON looks like this:

And, finally, we prettify the JSON using JSONFormatElements…

…so it ends up looking like this:

Note: Both JSONSetElement and JSONFormatElements will automatically alphabetize key/value pairs within JSON objects. This can be disconcerting if you aren’t expecting it, but eventually you work through the stages of grief and come to accept that it’s just the way things are. The JSON Data Interchange Standard definition at json.org specifies that “An object is an unordered [emphasis mine] set of name/value pairs”, whereas an array is an “ordered collection of values”. In other words, by definition the order of the key/value pairs within JSON objects simply does not, and should not, matter.

Simple Example #2

This script produces the same JSON as the preceding, but does it all in a single step:

Here, rather than invoking JSONSetElement twice, we invoke it once and use brackets to indicate multiple elements to be set… this trick with brackets allows us to write efficient code, and is similar to how brackets work in the Substitute function.

Simple Example #3

In this example, we start with the $$simpleJSON object created above, but now we want to add an array consisting of the customer ids in the found set. The array will be generated like so (bearing in mind that JSON arrays use a zero-based index)…

…producing a well-formed JSON array that looks something like this…

…and which can then be inserted into $$simpleJSON as the value portion of a new key/value pair, like so (note that the type is JSONArray)…

…and then prettified…

…yielding this.

Updating An Existing Element

Part of what makes JSONSetElement so powerful is that it can be used both to create new entries, and to update existing entries. Specify a valid address, and it will either create the entry if it doesn’t already exist, or update it if it does.

For example, given the above $$simpleJSON, we can change the “table” entry from “customers” to “contacts” like so:

Deleting An Element

When you need to remove an entry from your JSON, here is how you go about it:

JSONDeleteElement ( json ; keyOrIndexOrPath )

From a testing and debugging standpoint, a useful feature of this function is that if you leave the second argument empty, you get your original JSON, e.g.,

Okay, let’s delete something… how about the “customer ids” k/v pair we added in example #3 above?

What if instead of deleting the entire key/value pair, we simply want to remove the second value from the array (bearing in mind that JSON arrays are targeted via a zero-based index)?

Returning to this example…

…what if we want to delete multiple elements, e.g., remove both “record count” and “table” (and their corresponding values)?

You might think you could use brackets as we did with JSONSetElement, but that won’t work here. Instead you can either invoke JSONDeleteElement twice…

…or embed one instance within another.

Either way the two k/v pairs are removed and you end up with this:

Auto-Encoding/Decoding Control Characters

FileMaker’s JSON functions will encode and decode so-called “control characters” for you automatically, for example the tabs and hard returns here…

…are encoded by JSONSetElement as \t and \r…

…and then auto-magically decoded via JSONGetElement.

Double quotes and backslashes are elegantly dealt with as well, in terms of both encoding…

..and decoding…

Here is the complete list of control characters from json.org.

Nifty Unicode Hex Decoder Ring

As per the final entry in the above image, if you know the 4-digit hexadecimal Unicode value for a particular symbol, you can use JSONGetElement to render the symbol like so.

Passing Multiple Script Parameters

Old habits die hard, but as far as I’m concerned all the clever tricks and custom functions we’ve used over the years to pass multiple script parameters can (and should) now be permanently retired. JSON is ideally suited for passing multiple script parameters, because…

• it is widely understood
• it is infinitely flexible
• as we saw in the previous section, it effortlessly auto-encodes and auto-decodes potentially problematical characters

Here’s a simple example from last month’s Virtual List Reporting with JSON Arrays, where the parent script encodes and passes three subparameters (three $vars that have already been declared in the parent script)…

…which are then unpacked by the receiving script in a straight-forward manner.

I am a huge fan of “discursive” code like this, where the code itself makes clear what the intention is (effectively rendering the comment at line 1 unnecessary). Under certain circumstances, however, you may be seeking a way to auto-unpack a bunch of JSON-encoded subparameters into $vars without having to enumerate each one individually… in which case check out this custom function from Mislav Kos: JSONCreateVarsFromKeys.

(Speaking of custom functions, you may also want to investigate the extensive collection of FM-JSON Additions created by Todd Geist and company.)

Producing More Complex JSON

So far we’ve been looking at contrived and very simple examples, but we’re going to wind down with something more realistic. The demo file contains a basic order system for a winery, and consists of these tables…

…related like so:

Starting from a found set of customers, we’re going to produce JSON corresponding to all their orders, utilizing two different approaches which I’ve designated as “composite JSON” and “table JSON”.

The composite JSON script produces an array like so…

[ {object} , {object} , {object} ]

…where each object corresponds to one customer and all their orders. Drilling down into one object, we see that it consists of a series of key/value pairs…

{ city:value , custid:value , invoices:[array] , etc. }

…and if we dig into the array value for “invoices” we get this…

[ {object} , {object} , {object} , etc. ]

…where this time each object corresponds to one specific order. Here is an example of what one of the preceding objects might contain.

By contrast, the table JSON script produces a much “flatter” (less deeply nested) object consisting of four key/value pairs, corresponding to the four data tables in our system, e.g.,

{ customers:[array] , invoices:[array] , items:[array] , products:[array] }

…with each array consisting of multiple objects, and each of these objects representing a record in that particular table.

And I think that’s enough for today.