FileMaker JSON Serialization and Deserialization Gotchas

The native FileMaker JSON functions have been around for a while now, and as a result, the use of JSON within FileMaker solutions has become widespread. Some common uses include:

• Interacting with REST APIs
• Passing script parameters and results
• Tracking session data
• Displaying virtual lists
• Working with non-persistent data

There are a couple of gotchas that developers need to be aware of when serializing and deserializing JSON. (Serializing refers to converting values in FileMaker fields or variables into JSON data. Deserializing is the inverse operation – extracting a value from JSON to a FileMaker field or variable.) These behaviors stem from several causes, including:

• Mismatches between FileMaker and JSON data types
• File metadata
• Line endings

Mismatches Between FileMaker and JSON Data Types

A JSON data blob will either be a JSON object or a JSON array, but in FileMaker it will be represented as a text value. Within the JSON object (or array), the property values (or array values) can be one of several JSON data types. These types are shown in the table below, along with the FileMaker data types. Note that not all types in one column have a matching type in the other column.

While FileMaker fields have an explicit data type, the behavior for FileMaker variables is less obvious. The exact nature of how the FileMaker calculation engine determines variable data types is not documented, but variables seem to inherit the data type from the value assigned to the variable. For example, if you assign a text value, then the variable data type is text. In most cases, this works just fine, but there are some edge cases that can cause problems – we’ll show some examples of these in a moment.

Serializing and deserializing works as expected for some of the data types, such as Text «» String, so I will not cover those here. Instead, I will focus on the non-obvious behaviors.

Number Precision

FileMaker supports both very large numbers and numbers with a very high degree of precision. From the FileMaker Pro 18 Advanced technical specification, supported values are from 10^-400 up to 10^400 and the negative values of the same range.

The JSON implementation used by FileMaker does not offer the same range of support. Once the number exceeds the range, it is converted to scientific notation or truncated when serialized as JSON. This results in a loss of precision. Let’s look at some examples.

This 18 digit number is serialized properly:

JSONSetElement ( "" ; "x" ; 123456789012345678 ; JSONNumber )
= {"x":123456789012345678}

However, this 19 digit number is converted to scientific notation when serialized:

$json
= JSONSetElement ( "" ; "x" ; 1234567890123456789 ; JSONNumber )
= {"x":1.23456789012346e+18}

As a result, deserializing this number results in a loss of precision:

JSONGetElement ( $json ; "x" )
= 1.23456789012346e+18
= 1234567890123460000
≠ 1234567890123456789

Similarly, this number with 15 digits of precision is serialized correctly:

JSONSetElement ( "" ; "x" ; 0.123456789012121 ; JSONNumber )
= {"x":0.123456789012121}

And this number with 16 digits of precision is serialized in a way that results in a loss of precision:

JSONSetElement ( "" ; "x" ; 0.1234567890121212 ; JSONNumber )
= {"x":0.123456789012121}

This has obvious implications when working with large numbers or number with high precision, but typically record data does not involve such numbers. The more common use case is working with numeric UUIDs. If you are assigning numeric UUIDs to your ID fields, and you then pass the value of an ID field to a script using JSON, that value will change.

To get around the loss-of-precision issue, you can serialize numbers as JSON strings instead of JSON numbers.

Typecasting

If you serialize numbers as JSON strings to ensure that the number value does not change, you will need to typecast the value as a number upon deserialization. Typecasting refers to the process of explicitly declaring the type that you would like the data to be interpreted as. In FileMaker, we can use the GetAs family of functions to do this.

$ourNumber = GetAsNumber ( JSONGetElement ( $json ; "ourNumber" ) )

Why is this necessary? Suppose our number value is the JSON string “10”. If we deserialize it and do not typecast, FileMaker will treat this value as text, which can lead to some unexpected behavior:

$ourNumber < 2 // returns 1 (True)

This is equivalent to :

"10" < 2 // returns 1 (True)

When doing a less-than or greater-than comparison of two values, if one of those values is text, FileMaker performs a text-based comparison (referred to as collation). It compares the first character of each value. If they are the same, it compares the second character, and so on. In this case, the first character (“1”) comes before (i.e. is less than) 2, so the result comes back as True, even though we are likely to have expected it to be False.

Empty Numbers

If an empty value is serialized as a JSON number, the value will be set to zero. It will not be set to empty or null.

JSONSetElement ( "" ; "x" ; $emptyNumber ; JSONNumber )
= {"x":0}

This may not seem like much of a problem, but zero and empty are not the same things. If you later deserialize this value into a FileMaker variable or field and then test if the value is empty using IsEmpty( ), the result will be False – different than if you tested it before the serialization/deserialization.

The workaround is the same as before – to retain the empty value, you can serialize the number as a JSON string. Don’t forget to typecast the value using GetAsNumber() when deserializing. (In case you’re wondering, doing so will not turn an empty value into a zero.)

Alternatively, you can serialize the value as JSON null. But this complicates the logic of our calculations:

JSONSetElement ( "" ; "x" ; $emptyNumber ; if ( IsEmpty ( $emptyNumber ) ; JSONNull ; JSONNumber ) )

Dates, Times, and Timestamps

FileMaker dates, times, and timestamps do not have corresponding JSON data types. You can serialize these values as numbers or strings, but using numbers will render them human-unreadable in the sense that “10/1/2019” means something to us humans, and 737333 – FileMaker’s numeric representation of 10/1/2019 – does not.

If you serialize 10/1/2019 as a JSON string and then deserialize it, you will need to typecast it as a date. Otherwise, FileMaker will treat it as text. This is the same as when we deserialized numbers stored as strings before. And, like then, less-than and greater-than comparisons will behave differently than is likely expected.

To see this in action, put the following calculation in your Data Viewer:

Let ( [
	earlierDate = Date ( 2 ; 1 ; 2019 ) ;
	laterDate = Date ( 10 ; 1 ; 2019 ) ;
	json = JSONSetElement ( "" ; "key" ; laterDate ; JSONString ) ;
	laterDateFromJSON = JSONGetElement ( json ; "key" )
] ;
	List (
		earlierDate < laterDateFromJSON ; // returns 0 (False)
		earlierDate < GetAsDate ( laterDateFromJSON ) ; // returns 1 (True)
	)
)

The first list item in the previous example is functionally equivalent to:

GeAsDate ("2/1/2019") < "10/1/2019" // returns 0 (False)

Because the right side of the calculation has a text value, the less-than comparison is text-based, so the calculation evaluates as False even though February comes before (i.e. is less than) October.

Time and timestamp values have the same issue as dates. To work around these issues, serialize dates, times, and timestamps as JSON strings, and then typecast the values using GetAsDate, GetAsTime, and GetAsTimestamp upon deserialization.

File Metadata

JSON cannot handle binary values, but FileMaker container fields (or variables) can be base64-encoded and then serialized as a JSON string. This will preserve the file contents, but the file metadata – such as the file name and creation date – will be lost.

The file name can be fairly easily preserved like this:

$json
= JSONSetElement ( "" 
	; [ "myFileName" ; GetContainerAttribute ( $myFile ; "filename" ) ; JSONString ] 
	; [ "myFileContents" ; Base64EncodeRFC ( 2045 ; $myFile ) ; JSONString ] 
)

This can be deserialized as follows:

Base64Decode ( 
	JSONGetElement ( $json ; "myFileContents" ) ; 
	JSONGetElement ( $json ; "myFileName" ) 
)

But other metadata cannot be recovered with the tools that FileMaker provides. For example, when a signature is captured in FileMaker Go, the signature timestamp is embedded with the image as metadata. We can serialize this information the same as we did with the file name:

$json
= JSONSetElement ( "" 
	; [ "sigFileName" ; GetContainerAttribute ( $myFile ; "filename" ) ; JSONString ] 
	; [ "sigTimestamp" ; GetContainerAttribute ( $myFile ; "signed" ) ; JSONString ] 
	; [ "sigFileContents" ; Base64EncodeRFC ( 2045 ; $myFile ) ; JSONString ] 
)

However, we will not be able to reconstruct the file with the signature timestamp metadata using native FileMaker functions.

If you are sending container data to a REST API, and that API is able to correctly reconstruct the file with the supplied metadata, then this is not a problem. But if you would like to pass container data to another FileMaker file, for example as part of a script parameter, then this limitation will need to be accounted for in some other way.

Line Endings

When container data is base64-encoded, in some cases (depending on the type of base64 encoding being used), the resulting text will be split up into separate lines. The encoding type is governed by the RFC number – see this help article for more details. For example, when using the RFC #2045 type of base64 encoding, lines are split up at a maximum of 76 characters per line.

$base64
= Base64EncodeRFC ( 2045 ; $myContainer )

JVBERi0xLjMKJcTl8uXrp/Og0MTGCjQgMCBvYmoKPDwgL0xlbmd0aCA1IDAgUiAvRmlsdGVyIC9G
bGF0ZURlY29kZSA+PgpzdHJlYW0KeAGNkTFPxDAMhff8ije2SOk1idu0Y+/gBiZOisSAmCpODFek
0v8v4aSJAYkBZfAn23mxX1ZcsMIMoBad7+Ed4fMNz/jA4bQZzBtMOtscu7wnLOhGSnRLxDdvXFM5
...
MTA1PiBdID4+CnN0YXJ0eHJlZgoxMzM0NQolJUVPRgo=

Each of the encoding types that splits content onto separate lines uses CRLF line endings, which means that each line is separated with a carriage return (CR) followed by a line feed (LF). The CRLF line separator is represented with a single UTF-16 character, which corresponds to code 1000013.

Code ( Middle ( $base64 ; 77 ; 1 ) )
= 1000013

When this base64-encoded text is serialized into JSON, the CRLF character is converted to \r and \n.

$base64JSON
= JSONSetElement ( "" ; "myContainer" ; $base64 ; JSONString )

{"myContainer":"JVBERi0xLjMKJcTl8uXrp/Og0MTGCjQgMCBvYmoKPDwgL0xlbmd0aCA1IDAgUiAvRmlsdGVyIC9G\r\nbGF0ZURlY29kZSA+PgpzdHJlYW0KeAGNkTFPxDAMhff8ije2SOk1idu0Y+/gBiZOisSAmCpODFek\r\n0v8v4aSJAYkBZfAn23mxX1ZcsMIMoBad7+Ed4fMNz/jA4bQZzBtMOtscu7wnLOhGSnRLxDdvXFM5\r\n...MTA1PiBdID4+CnN0YXJ0eHJlZgoxMzM0NQolJUVPRgo=\r\n"}

But in the reverse process, the \r\n characters are deserialized as a carriage return only (code 13).

Code ( Middle ( JSONGetElement ( $base64JSON ; "myContainer" ) ; 77 ; 1 ) )
= 13

In other words, the CRLF line endings are lost and CR is used instead.

This behavior doesn’t seem to pose problems when reconstructing the file using the Base64Decode function. The following still works correctly:

Base64Decode ( JSONGetElement ( $base64JSON ; "myContainer" ) ; "myFile.txt" )

But it could pose problems if the consumer of the deserialized base64-encoded value is expecting CRLF line endings and – unlike the Base64Decode function – will not accept CR line endings.

Conclusion

The situations where non-obvious serialization and deserialization behaviors occur are admittedly edge cases. You will not encounter these scenarios most of the time. But these are exactly the kinds of errors that can be tricky to debug.

To guard against them, develop a habit of explicitly typecasting values when deserializing JSON. Consider using JSON strings even for number values. And when working with container data, be aware of the fact that base64-encoding does not include file metadata and that FileMaker converts CRLF line endings to CR during deserialization.