Thinking About JSON, part 1

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

I’ve been working on a couple large JSON projects over the last few months, and with the one year anniversary of FileMaker having built-in JSON capabilities just around the corner, this seems an opportune moment to share some reflections and opinions (some of which may contradict JSON-related opinions I have expressed previously).

Demo file: json-sandbox.zip

This article is intended to be a structured introduction to JSON in FileMaker. There are some other good FM/JSON resources out there, and I recommend these in particular:

• DB Services: FileMaker JSON Functions
• Skeleton Key: FileMaker 16 – New JSON Functions
• Lumin Fire: FileMaker 16’s New JSON Functions
• FileMaker Pro 16 Help: Working With The JSON Functions

At any rate, if you’re not yet completely comfortable with JSON perhaps some of the following will be helpful, or failing that, amusing (intentionally or otherwise).

#0. An Attempt At Humor

Q. Are you familiar with { “ABC” : 123 } ?

A. Of course. It was a monster #0 hit for the JSON 5 in 1970.

(Sorry, couldn’t resist.)

#1. Dive In, The Water’s Great

JSON has a small, well-defined and logical set of rules, enabling you to encode, store and retrieve structured data in a format that is easily readable by both humans and machines. It has become the data exchange format on the Web, and if you aren’t already working with it, chances are you will be either producing it, consuming it, or both, some time soon.

Reading about JSON is fine, but you will learn better by doing, so if you don’t have a JSON test bed file handy, go ahead and download the demo file accompanying this article. It contains various JSON examples you can experiment with in the data viewer.

In my experience having a sandbox (i.e., consequence-free environment) to play in is a great way to learn. And of course you should feel free to modify existing records, or add new records to store JSON examples you create yourself or find elsewhere.

#2. JSON Topography

When learning a foreign language, you get a sense of the “topography” (overall structure) of the language fairly quickly… for example, though you may not know the meaning of a particular word, you can typically use contextual clues to identify the part of speech it belongs to.

Well, good news, the topography of JSON is far simpler than any human language, because it is built on just two structures…

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

Note 1: apart from the {} vs [], a big difference between objects and arrays is that objects have key:value (k:v) pairs, whereas arrays have values only.

Note 2: keys are wrapped in quotes, as are text values, but other values are not.

Note 3: white space in JSON is ignored.

Note 4: The expressions “key:value pairs” and “name:value pairs” are interchangeable. I prefer the term “key” because the function to list keys is called JSONListKeys (not JSONListNames).

So much for the basics. Where things get interesting is that a “value” can itself be JSON (i.e., an object or an array), as opposed to the simple text and number elements shown in the above examples.

If necessary, read the preceding sentence again. A key concept is that a given blob of JSON often contains other smaller blobs of JSON. And those smaller blobs of JSON are valid “values” within both objects and arrays.

For example, this JSON from the FileMaker help example is, at the top level, an object (you can tell because it begins with a left brace).

So we begin with an…

{object}

… and this object consists of a k:v pair where the key is “bakery” and the value is another JSON object.

{ "bakery" : {object} }

Expanding that object, we again have a key (“product”), but this time the value is an array.

{ "bakery" : { "product" : [array] } }

And if we dig a level deeper we can see that the array has three objects, …

{ 
    "bakery" : { 
        "product" : [ {object} , {object} , {object} ] 
    } 
}

…each of which contains a series of k:v pairs where, finally, the values are actual data.

The big lesson here is that everything in JSON is either an object or an array (or an internal component thereof) and these structures can be nested and combined at any level of depth and complexity to accurately depict whatever it is you’re trying to model.

This makes JSON incredibly powerful, not to mention mind-blowingly cool.

Here’s another example:

Though it looks very different from the preceding example, this is 100% valid JSON, consisting of an array containing two other arrays, i.e., this…

[array]

…which contains this:

[ [array] , [array] ]

#3. Validating JSON

A moment ago I used the term “valid JSON”, and a simple way to test JSON for validity is to point JSONFormatElements at it and see what comes out the other side. This is valid JSON…

…whereas this is not:

Valid JSON will emerge from JSONFormatElements with either a “{” or a “[” as the leftmost character, so I like to test for validity like this…

…or like this. Either test returns a 1 if the JSON is valid, or a 0 if it is not.

Note 1: there are custom functions out there that can simplify this task further. My inclination is to really understand something before I use a custom function to simplify things, but that’s a matter of personal choice… and one which can vary depending on the situation.

Note 2: JSONFormatElements will not only attempt to prettify your JSON, it will also arrange object keys in alphabetical order. This may be inconvenient at times in terms of human readability, but the order of keys (a.k.a. names) is irrelevant in JSON, as per this from json.org: “An object is an unordered [emphasis mine] set of name/value pairs”, whereas an array is an “ordered collection of values”. So, to be clear, as far as JSON is concerned, order is meaningful within arrays but not within objects. More on this in section #6 below.

#4. Fixing Invalid JSON

Once you understand the topography of JSON and how to validate it, you are well on your way to being able to fix problematical JSON that you encounter in the wild. For example, recently I needed to bring some JSON containing zip code data into FileMaker. Here’s a small sample of what I started with:

What can we say about this? At the macro level we appear to have…

{object}{object}{object}

…which looks an awful lot like an array, minus the outer brackets and commas between values. If we transform it like so…

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

…it just might validate; and if not, we’ll be a step closer at any rate. Let’s give it a try. (Reminder: horizontal and vertical white space is optional — I like to include it for readability.)

Okay, looks good in the data viewer. Let’s paste the result into the sandbox…

…and see whether JSONFormatElements likes it.

And indeed it does. Each object contains five k:v pairs, and note that the “loc” key in each object has for its value a tiny array consisting of two values: [longitude,latitude], whereas the values for the other k:v pairs are plain text and numbers.

#5. Reading JSON — Objects

Given this block of JSON (which contains only objects)…

…we can use JSONListKeys to retrieve the top level keys (a.k.a. TLKs), and you will observe that they are returned in alphabetical rather than original order.

Did you notice that I intentionally left the second argument empty in the above example? That was because I wanted to list the TLKs. But we can list keys at any level by specifying the appropriate “path” in the second argument, e.g.,

…and note that JSON paths are case-sensitive; for example, this returns nothing.

Going a level deeper, and making sure our path case is correct, we can use a “.” to delimit the segments in the object path.

What happens if we try to go down one more level?

The result is empty, because JSONListKeys only returns results when the specified path leads to an object or an array. Here’s the example again.

Returning to this query…

…what if we want to retrieve the corresponding values for those keys? In that case we can use JSONListValues.

The values are returned in the alphabetical order of the corresponding key. This is a good thing, because it means that items returned by JSONListValues will always correspond with items returned by JSONListKeys in terms of list position (assuming, of course, that you use the same arguments in both cases).

Actually I just said “always correspond”, but there is one exception: if your JSON contains nulls, JSONListValues will ignore them.

A work around if you want to maintain list position parity would be to ensure your JSON contains “” instead of nulls, e.g., [ 0 , “” , 2 ] vs. [ 0 , null , 2 ].

Returning once again to this example…

…and the query we saw a moment ago…

At this point it might seem reasonable to tack “.iso” onto the end of the path in the preceding example — after all “iso” is a key, and it does have a value…

…but JSONListValues doesn’t work that way. Like JSONListKeys, it reaches down the “lookup.time.iso” path, fails to locate an object or an array, and returns nothing.

So how can we extract the value at that location? By instead using JSONGetElement, that’s how. In other words, same path, but different function, like so:

What happens if we go up one level, and specify “lookup.time” as the path?

The result is the value corresponding to that particular key, and in this case the “value” happens to be a JSON object.

The distinction is important: JSONGetElement can return a discrete value, or it can return JSON (i.e., an object or an array), depending on the path you provide. If the path leads anywhere, JSONGetElement will tell you what it finds there.

By contrast, as implied by their names, JSONListKeys and JSONListValues return lists (which in some cases may resemble JSON, but will not be well-formed — see below for an example of this), but only when the path leads to an object or an array.

#6. Reading JSON — Arrays

So much for objects; what about arrays? Here’s an example of an array containing seven values, each of which is itself an array containing five values:

Given that arrays don’t have keys, only values, what happens when we point JSONListKeys at this example?

We get back a list of addresses indicating the position of each value within the outermost array. Since JSON uses a zero-based index, the first item = 0, the second = 1, and so on.

And if we point JSONListValues at the same location, we get the seven values corresponding to the preceding. (This is the “looks like JSON but is not well-formed” example I referred to a moment ago.)

Returning to our well-formed JSON…

When working with array paths, since we don’t have named keys to work with, we instead use index numbers expressed via array notation, i.e., [n]. So, bearing in mind that JSON uses zero-based indexing, we can get the third “value” (which happens to be an array) like so…

…and digging down one level, we can extract the first value from that array like so:

(If you’re curious about “+” signs in dates see JSON + Virtual List, part 2.)

#7. Reading Blended JSON

We’re going to wind up today with the most complicated example we’ve seen thus far.

In terms of topography, we have this…

[array]

…which expands to this…

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

…which further expands to this…

[ 
{ k:v , k:v , k:v , k:v , k:v , k:v } , 
{ k:v , k:v , k:v , k:v , k:v , k:v } ,  
{ k:v , k:v , k:v , k:v , k:v , k:v }
]

…and then to this…

[ 
{ k:v , k:v , k:v , k:v , "batter":[array] , "topping":[array] } , 
{ k:v , k:v , k:v , k:v , "batter":{object} , "topping":[array] } ,  
{ k:v , k:v , k:v , k:v , "batter":[array] , "topping":[array] }
]

…and we could keep going, but you get the idea.

Since the top level structure is an array, JSONListKeys returns this:

…corresponding to the…

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

…we saw a moment ago. Focusing in on the second object, i.e.,

…we will utilize “[n]” and/or “.” in our paths as needed. Some examples:

Note that in these examples (except for the final one), each step downward alternates between an array and an object, so the path alternates between [n] to address the array segment, and a named key to address the object segment.

The final example, “[1]batter.type”, uses a period to indicate that we are moving from one object into another object. It appears that out in the wider JSON world, “.” is always used as a separator between path segments (a.k.a. “legs”) regardless of whether those segments represent objects or arrays. My current practice with FM/JSON is to only use a “.” in a path when necessary, i.e., to indicate a parent/child relation between two objects, because that is the only time that FileMaker requires it.

As far as FileMaker is concerned, when one or both of the adjoining segments represent arrays, the “.” is superfluous, as per these examples:

As the saying goes, YMMV (your mileage may vary)… and I think that’s about enough for today. In part 2 we’ll look at creating JSON, as well as a few miscellaneous odds and ends.