Working with JSON data in FileMaker

by Simon Brown
Posted in All, bBox, FileMaker, JavaScript, Techniques

There are a number of ways of working with JSON data in FileMaker. At least two typical ones are:

  • Custom Functions based parsing & encoding
  • using a WebViewer & JavaScript

JSON however doesn’t parse easily with FileMaker’s text handling functions. And using a WebViewer has a number of complications, the main one being the awkwardness of getting data in & out of the WebViewer. Some other solutions only parse JSON, but don’t encode it. So I am going to propose yet another way.

This does have its own dependencies however. The bBox plugin and a small amount of Python scripting will be required. But the combination makes for a very concise implementation.

Basic JSON Encoding

Python has a number of good, robust, and fast JSON modules. For many purposes, the standard json module will do just fine.

Using this module, here is a complete FileMaker expression that queries the database and returns a JSON string:

Set Variable [
$script;
Value:
"import json,fm" & ¶ &
"r = fm.executesql ("SELECT id,category,name FROM EXAMPLE WHERE enabled_t = 'Y'")" & ¶ &
"print json.dumps (r)" ]

 

Loop
Exit Loop If [ bBox_PythonCompile (0; $script) ]
Set Variable [ $result; Value:bBox_PythonExecute (0) ]
Set Variable [ $ignore; Value:bBox_PythonFinalize ]
Exit Loop If [ True ]
End Loop

A representative sample of the result yields this:

[[95.0, "Shell", "Last went to sleep"], [90.0, "Shell", "Calendar for year"], [110.0, "Sort", "Remove duplicate values and ignore leading blanks"], [111.0, "Sort", "Reverse sort, ignore case"],
...
[139.0, "Curl", "SOAP request - convert temperature"], [140.0, "Curl", "SOAP request - location for zip"], [141.0, "Curl", "SOAP request - IP location"], [142.0, "PasteboardSet", "XML as FileMaker tables"]]

So what happened here?

  • set a variable to the contents of the Python script to run
  • created a Python VM and had the script compiled
  • executed the Python script
    • imported the needed Python modules
    • executed a SQL request in FileMaker, which was returned as a Python list
    • asked the json function to convert the Python list object into JSON text
    • Python returned the JSON string to FileMaker
  • Tore down the Python VM

It is normally easier to store the Python scripts in a field somewhere, since we must explicitly add the returns to the text when setting them via a FileMaker expression, but I’ve done it this to show you a full self-contained example.

Also, it is not a requirement that you call bBox_PythonFinalize. As a matter of fact, if you are going to re-run the same script again, you might be better off not calling it. Using either the fm.evaluate Python function or the FileMaker bBox_PythonSetVar function to pass the SQL expression might be one way to help re-use a previously compiled script.

JSON With Name & Value Pairs

Typically, JSON data comes in key/value pairs, so our first example is arguably not fully formed JSON yet. One way to add in key/value pairs is to append the keys to every value in the SQL result, and then convert that into a Python dictionary (dict) object. This sounds complicated and slow, but the performance hit is actually very slight, and by using a construct called a list comprehension, also easy to implement. While we are at, I’ll factor out the column names from the SQL.

To do this the FileMaker script is unchanged, so I’ll just show the FileMaker Set Variable script step used to set the Python script:

Set Variable [
$script;
Value:
"import json,fm" & ¶ &
"fields = ('id','category','name')" & ¶ &
"r = fm.executesql ("SELECT " + ",".join(fields) + " FROM EXAMPLE WHERE enabled_t = 'Y'",fields)" & ¶ &
"resultAsDict = [(dict (zip (fields,row))) for row in r]" & ¶ &
"print json.dumps (resultAsDict)," ]

Running this new version against the demo database gives this result:

[{"category": "Shell", "id": 95.0, "name": "Last went to sleep"}, {"category": "Shell", "id": 90.0, "name": "Calendar for year"},
...

So far we’ve only discussed encoding into JSON data. In a next post, I hope to discuss some ways you can decode it.

Simon

Leave a Reply