Writing a calculation function: json2sql

After spending two days writing a calculation function, I figured I wasn’t a day short, and that sharing the story behind writing this function could be interesting for other developers—not so much for the function itself, but to capture the mindset and the general ideas that help approach a complex function.

Moreover, since Claris has honored me with a spot at the Engage conference next March in Austin, with a topic as unusual and pretentious as “how I, Fabrice Nordmann, tackle a problem to solve with the FileMaker platform,” I need to start seriously reflecting to understand my own process in order to explain it. As readers, you’re serving as my test audience, for which I thank you.

First, let’s explain what the function does.

The function is called json2sql and enables the formulation of an SQL query for FileMaker using a JSON parameter.

Indeed, the JSON writing function, JSONSetElement, became quite enjoyable to use with FileMaker 21, and you quickly get used to it, while writing SQL remains cumbersome—especially if you want to write it well, without dependency on field names, so that the query doesn’t break if a field is renamed.

The idea, therefore, is to pass a JSON parameter to this function, have it translate the parameter into an SQL query, and execute it (that was the initial idea, but things got much more complicated afterward, as you’ll see).

We already had a function that we use frequently, sql.match (_requestedField ; _matchField ; _match), which allows retrieving values from a column (_requestedField) in records matching a criterion in a query of this type.

SELECT _requestedField WHERE _matchField = _match

It allows us to handle a large portion of the queries we need when coding in FileMaker.

But, of course, it’s very limited. If you want multiple columns, functions, multiple comparisons (WHERE)… it’s not enough.

We also have a set of functions that help us write SQL cleanly, like **sql.table**, **sql.col**, **sql.in.clause**… but nothing as cool as JSON.

Plus, there were dirty dishes in the sink, so it was clearly the perfect moment to come up with a new function to write.

So here we go!

First, what should be the “signature” of this function? What name? What parameters?

I like to keep things simple, so:

json2sql ( _json )

Exactly, JSON is already designed to structure information, so I’ll include everything in the JSON—it looks nice. (Spoiler: it won’t end up that way.)

So, I’m thinking about the structure of the JSON, keeping in mind that the function ultimately needs to call the native function:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

If you’re not familiar with the ExecuteSQL function, let’s recap: sqlQuery is a SELECT-only SQL query. You can use ? placeholders instead of comparison values, which FileMaker will replace with items from the argument list at the end of the function, in order. The advantage of this approach is that FileMaker handles data types for you (converts dates to SQL format, adds quotes around text but not numbers, manages decimal separators, etc.).

For example, if I write:

ExecuteSQL ( "SELECT primaryKey FROM invoices WHERE clientName > ? AND invoiceDate = ? AND totalAmount > ?" ; "" ; "" ; "L" ; Date ( 6 ; 1 ; 2024 ) ; round ( 100/3 ;2 ) )

Then, FileMaker will interpret it automatically and execute the following query:

SELECT primaryKey FROM invoices WHERE clientName > 'L' AND invoiceDate = '2024-06-01' AND totalAmount > 33.33

(adds single quotes, converts the date, and uses a period as the decimal separator, even if my file uses a comma, since SQL interprets the period).

That’s our quick refresher on ExecuteSQL; now, let’s get back to writing the function.

We’ll have a JSON structured using a function like:

JSONSetElement ( ""
   ; [ "query" ; <a complex JSON object I will think about later> ; JSONObject ]
   ; [ "fieldSeparator" ; "" ; JSONString ]
   ; [ "rawSeparator" ; "" ; JSONString ]
   ; [ "arguments" ; <a JSON array, since arguments must be ordered> ; JSONArray ]
)

Note that I wish to use these notations

• "{table}"."{column}" instead of simply {column}

• "{table}" instead of {table}

This approach helps avoid issues with SQL reserved names and oddly named fields, like those with spaces. It’s essential, in my opinion, to adopt this mindset: everyone should be able to use this function in any FileMaker file. I make no assumptions that fields will be named “correctly” or that the decimal separator will match mine, or that the language will be French (even though I personally use the English version of FileMaker). So, always think “ubiquity”: the function should work everywhere, and if it can’t, that limitation should be deliberate, acknowledged, and documented.

The “algorithmic” structure of the query is therefore:

"SELECT" & [ loop over an ordered list or array of column names ] & " FROM " & [ table, which is information I can extract from the same list if I fully qualify field references with the table::field notation ]

followed by an optional section (criteria aren’t mandatory):

& " WHERE " & [ loop over an array of criteria composed of a logical operator (except for the first one), a column, a comparison operator, and a value ]

I finally dive into writing a JSON prototype for the query parameter.

So I open the Data Viewer and…

JSONSetElement ( ""
   ; [ "query.columns[+]" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
   ; [ "query.columns[+]" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
   ; [ "query.columns[+]" ; GetFieldName ( invoice::amount ) ; JSONString ]
)

Here’s the first array—the list of column names (this is indeed the name, not the value, so the type is always String).

Except… I realise I forgot about functions. I know I don’t want to handle them just yet, but my structure needs to allow for future expansion. I’m also keeping in mind that if I ever want to manage joins, I’ll need a bit more information about the columns, but that’s for later. Starting over:

JSONSetElement ( ""
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
   ; [ "query.columns[:].function" ; "Sum" ; JSONString ]
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::amount ) ; JSONString ]
)

Good, now I have an array representing the list of columns, and I need to do something with it.

First and foremost, I need an environment for this. So, I write in my Data Viewer:

Let ([
   _json = JSONSetElement ( ""
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
         ; [ "query.columns[:].function" ; "Sum" ; JSONString ]
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::amount ) ; JSONString ]
      );
   _query = ""
];
   _query
)

"SELECT " & While ([
        j = _json ;
	          c = ValueCount ( JSONListKeys ( j ; "columns" )) ;
	          i = 0 ;
	          r = "" ;
	          v = "" ;
	          to = "" ;
	        toq = "" 
	     ];
	        i < c ;
	     [
		        v = Substitute ( JSONGetElement ( j ; "columns[" & i & "].name" ) ; "::" ; ¶ ) ;
		        function = JSONGetElement ( j ; "columns[" & i & "].function" ) ;
		        // as we are selecting from 1 table only, let's extract the table occurrence name only during the first iteration
		        to = Case ( i = 0 ; GetValue ( v ; 1 ) ; to ) ;
		        // quoted version, to avoid issue with reserved words or blanks
		        toq = Case ( i = 0 ; Quote ( to ) ; toq ) ;
		        column = GetValue ( v ; 2 ) ;
		        colq = Quote ( column ) ; // quoted version
		        // append to the list
		        r = List ( r ; Case ( IsEmpty ( function ) ; colq ; function & "(" & colq & ")" )) ;
		        i = i+1
	     ];
		        Substitute ( r ; ¶ ; ", " ) & " FROM " & to 
   )
)

First, rest assured, I didn’t write this all in one go. But I can’t dissect each step, or you’d get bored, and the dishes in the sink would start to smell…

A few things to note:

• I didn’t handle functions right away.
• I follow several conventions for my While loops, which make the process faster:
  • The iterator is always i (except in nested loops, where it’s j).
  • I start with c (the number of iterations) and initialize i (the iterator), r (the result), and usually v (the processed value).
  • The condition is almost always i < c.
  • Incrementing the iterator (i = i+1) is in the first parameter of the logical part or last when working on a JSON array (zero-based).
  • I only added comments later.

You might also wonder why I have to and toq, column and colq, instead of just the q (quoted) versions. You’re right; at this stage, it’s not necessary. I left them here to make comparison with future versions easier (saving you some work!).

WHERE clause

I started with the criteria.

I began writing the array, but at that point, I needed some data—or at least a table and fields for testing.

I asked ChatGPT to provide me with a sample dataset in CSV format, making sure it included various data types (text, including at least one column with multi-line text, numbers, and dates) and duplicates (to enable grouping). I also ensured that some column names included spaces or accented characters. I dragged the CSV file onto the FileMaker icon, which converted it into an .fmp12 file, and voilà, I was ready to work “for real.” Well, almost. I first needed to convert the date data from the YYYY-MM-DD format to FileMaker’s format, DD/MM/YYYY in my file. That should have clued me in, but… you’ll see.

Then, back to the Data Viewer.

JSONSetElement ( "" 
   ; [ "criteria[+].column" ; GetFieldName ( invocies::total ) ; JSONString ]
   ; [ "criteria[:].operator" ; "<" ; JSONString ]
   ; [ "criteria[:].value" ; 100.23 ; JSONNumber ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::invoiceNumber ) ; JSONString ]
   ; [ "criteria[:].operator" ; "LIKE" ; JSONString ]
   ; [ "criteria[:].logicalOperator" ; "OR" ; JSONString ]
   ; [ "criteria[:].value" ; "NC%" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::clientVatNumber ) ; JSONString ]
   ; [ "criteria[:].operator" ; "=" ; JSONString ]
   ; [ "criteria[:].value" ; "*" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::taxTotal ) ; JSONString ]
   ; [ "criteria[:].operator" ; "=" ; JSONString ]
   ; [ "criteria[:].value" ; "=" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::date ) ; JSONString ]
   ; [ "criteria[:].operator" ; ">" ; JSONString ]
   ; [ "criteria[:].value" ; Date ( 12 ; 1 ; 2015 ) ; JSONString ]
)

As you can see, with the new [+] and [:] notation in version 21, it’s incredibly easy to write an array naturally. With [+], I start a new row, and with [:], I add a “column” (“attribute”) while staying in the same row.

This allows me to write in a very natural way, and new ideas come to mind as I go. For example, I thought of using common, practical search operators in FileMaker, such as “=” (empty) and “*” (not empty). So, I included these cases in the prototype, planning to map them to IS NULL and IS NOT NULL in SQL. What’s important here is that I hadn’t considered this before starting, but as I worked, I realised it would be manageable and decided to integrate it right into the first version.

First pitfall

On the other hand, while writing this prototype, I realize something I hadn’t thought of (I should have, but I must face the truth, even if it disappoints my mother: I am imperfect).
Note: For my grandmother, whom I mentioned earlier (article in French), it’s all black or white; she has a very Boolean way of thinking.

And so, the issue is that when it comes to date comparison, I realize that if I pass the arguments as ?, as I initially wanted, so they can be substituted with values (see above on the ExecuteSQL syntax), the type will inevitably be lost through JSON, as JSON lacks a date type. As a result, FileMaker won’t be able to pass the correct data type, seeing only text or a number.

This adds to what I already had in mind: to pass a variable number of parameters to a function, as indicated by {}(optional) and … (variable number) in the function’s signature:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

This mishap led me to reconsider the signature I had chosen, with a single JSON parameter.

Although I could have continued in this direction, I realized that including fieldSeparator and rowSeparator parameters in the JSON wouldn’t highlight the difference from ExecuteSQL or the way arguments are passed. I decided to revise this approach and move to the following signature:

json2sql ( _jsonQuery ; _fieldSeparator ; _rowSeparator )

which resembles the signature of the native function more closely:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

and it lightens the documentation that the user (developer) will need to read to use the function.

This, too, is part of the “mindset” I adopt when writing a custom function: I aim to respect the conventions of the FileMaker platform and to be as consistent as possible. I’m not giving up the underscores before the parameters, though; firstly, they remind the user that this is a custom function, and secondly, it’s a convention I find quite important in my code. But let’s not get into naming conventions…

I then add to my function the part that handles the criteria (the WHERE clause):

<the first part of the function here>
& Case ( not IsEmpty ( JSONListKeys ( j ; "criteria" )) ; 
   " WHERE " &
   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "criteria" )) ;
      v = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "criteria[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      o = JSONGetElement ( j ; "criteria[" & i & "].operator" ) ;
      o = Case ( IsEmpty ( o ) ; "=" ; o ) ; // default comparison operator is =
      lo = JSONGetElement ( j ; "criteria[" & i & "].logicalOperator" ) ; 
      lo = Case ( IsEmpty ( lo ) ; "AND" ; lo ) ; // default logical operator is AND
      value = JSONGetElement ( j ; "criteria[" & i & "].value" ) ; 
      type = JSONGetElementType (  j ; "criteria[" & i & "].value" ) ;
      // this ugly things converts the data to JSON then parses it as a string to handle decimal separators and single quotes.
      v = Case ( type = JSONNumber or type = JSONBoolean ; Substitute ( JSONSetElement ( "" ; "v" ; value ; JSONNumber ) ; [ "{\"v\":" ; "" ] ; [ "}" ; "" ] ) ; "'" & value & "'" ) ;
      r = r & Case ( i ; " " & lo & " " ) // the logical operator is omitted for the first criterion
          & Quote ( f ) & " " & Case ( o = "=" and ( IsEmpty ( value ) or value = "=" ) ; "IS NULL" ; o = "=" and value = "*" ; "IS NOT NULL" ;  o & " " &  v ) ;
      i = i+1 
   ] ;
      r
   )
)

A few comments:

• This part is conditional on finding a criteria array in the JSON. I could have included the ” WHERE ” directly in the While result, but I find this approach more readable. It’s immediately clear that it’s the WHERE clause of the query.
• For the comparison operator (o), if it’s empty, I default to “=”. Considering default values helps make the function easier to use.
• For the logical operator, the default is AND.
• As anticipated, handling * and = is very straightforward (see the definition of r).

I’ll briefly cover the other clauses: GROUP BY, ORDER BY, OFFSET, and FETCH FIRST. They’re fairly straightforward in the function’s code.

Just note that I intentionally excluded OFFSET by percentage, as it introduced ambiguity in the JSON parameter—should I send a number (5) or a text (“5%”)? Also, I’ll admit that I’ve never used the percentage offset, so I didn’t want to add unnecessary complexity, at least not in the first version.

// GROUP BY
& Case ( not IsEmpty ( JSONListKeys ( j ; "group" )) ; " GROUP BY " &

   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "group" )) ;
      f = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "group[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      r = List ( r ; Quote ( f )) ;
      i = i+1 
   ] ;
      Substitute ( r ; ¶ ; ", " )
   )
)

// ORDER BY
& Case ( not IsEmpty ( JSONListKeys ( j ; "sort" )) ; " ORDER BY " &
   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "sort" )) ;
      d = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "sort[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      d = JSONGetElement ( j ; "sort[" & i & "].dir" ) ; 
      d = Case ( IsEmpty ( d ) ; "A" ; d ) ;
      r = List ( r ; Quote ( f ) & Case ( Left ( d ; 1 ) = "d" ; " DESC" ; " ASC" )) ;
      i = i+1 
   ] ;
      Substitute ( r ; ¶ ; ", " )
   )
)

// OFFSET and FETCH FIRST
& Case ( JSONGetElement ( j ; "offset" ) ; " OFFSET " & JSONGetElement ( j ; "offset" ) & " ROWS" )
& Case ( JSONGetElement ( j ; "limit" ) ; " FETCH FIRST " & JSONGetElement ( j ; "limit" ) & " ROWS ONLY" )

SQL execution, and it’s done!

And so, here I am with a function that creates a perfect SQL query from a JSON parameter. All that was left was to execute the SQL and return the result.

Nothing could be simpler.

A few tests. It works like a charm! The queries work perfectly—I get the correct results, with or without functions, with or without criteria…

And then… I had an idea.

And that’s when things started to go sideways…

…but I’ll tell you all about it in part two.

Conclusions of Part One

• Get into a specific mindset. Here, I know I’m working on a generic function, so it must be capable of operating in any context and be easy for any developer to use. When I create a function for a client project, I prefix the function name with the project name, and at the end of the project, I review these functions to see if any should be made generic and added to our toolkit. But if some qualify, I NEVER do so without reviewing the code to ensure it’s consistent with our other functions, and that it accounts for cases we didn’t need in this specific project.
• Respect the FileMaker platform whenever possible and write functions in a way that makes them naturally usable within that context.
• Thinking about handling default values prevents forcing the user (the developer) to specify all parameters.
• Plan, but not too much. I have an idea of what the function needs to do, and I defer some possibilities right away (like joins in this case), but on the other hand, I don’t close myself off to ideas that may come up along the way (such as handling the comparison operators = and * in this example). As you’ll see in the second part, having an idea midway through cost me a lot of time, but on the other hand, the result was worth the effort.
• Work with data. Start with concrete examples and move towards abstraction. This approach is far more efficient than building an entire mental structure, only to confront it with reality later and realize it doesn’t work.
• Think about data types, and keep in mind that when looping (While), multi-line texts are a data type in themselves.
• Have conventions (like the While loop structure here, among others) and stick to them.