Thinking About JSON, part 1

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

The following is intended to be a series of observations, rather than a structured introduction to JSON in FileMaker. If you’re looking for the latter, I recommend these resources in particular.

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.

2018-04-20_100428

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), rather than the simple text and number values 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).

2018-04-18_181801

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.

2018-04-19_093705.png

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:

2018-04-20_121653.png

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…

2018-04-20_121352.png

…whereas this is not:

2018-04-20_122556

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

2018-04-20_124004.png

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

2018-04-20_152348

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:

2018-04-21_113133.png

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.

2018-04-21_122212

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 actual data elements.

#5. Reading JSON — Objects

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

2018-04-22_104335

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

2018-04-22_104917.png

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

2018-04-22_105631.png

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

2018-04-24_100523

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

2018-04-22_105829

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

2018-04-22_110016.png

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.

2018-04-22_104335

Returning to this query…

2018-04-22_105829

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

2018-04-22_111554.png

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

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…

2018-04-22_150415

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

2018-04-22_151007

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

2018-04-22_151709

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:

2018-04-22_225933

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

2018-04-22_230524

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

2018-04-23_120545.png

Returning to our well-formed JSON…

2018-04-22_225933

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…

2018-04-22_234947.png

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

2018-04-23_123418.png

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

2018-04-23_150014.png

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:

2018-04-23_155110.png

…corresponding to the…

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

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

2018-04-23_155447

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

2018-04-23_1612182018-04-23_161449.png

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:

2018-04-23_163618.png2018-04-23_171955

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.

9 thoughts on “Thinking About JSON, part 1

  1. Howard Schlossberg

    Thanks for the easy-to-follow instruction, Kevin. Especially helpful for those of us who have long been parsing JSON simply by trial and error (or other tactics)!

    Reply
    1. Kevin Frank Post author

      Thanks Howard. That describes my experience as well. Having gone directly from crawling to trying to run vis-a-vis JSON, this article was a remedial attempt at a “walking stage”.

      Reply
  2. shiva105

    An interesting problem I’ve had to solve is to retrieve a value from a JSON API payload, where the value is in an array, but I don’t know which position in the array. The only solution I’ve found so far is to revert to tried-and-true parsing techniques- finding pattern matches and using the Middle() function. Not a very elegant solution, but workable.

    Reply

Leave a Reply

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