In early 2017 I became interested in the question of whether I could write a FileMaker custom function which would allow me to return ExecuteSQL results in the form of JSON. My typical use-case was grabbing data that I would then feed to code within a WebViewer. Note that this was never an essential development requirement -- just something that I became curious about.
• Well-formed and properly encoded JSON output
• Reasonable to fast performance for a row count of around 5000 or less
• Intuitive to use, and easy to drop into a solution
• Support for output in a JSON structure beyond flat key/value association.
• Avoid use of recursion in processing the SQL output.
I'd like to share the suite of three Custom Functions which resulted from this inquiry.
As an introduction:
There are a couple of YouTube videos which illustrate the concepts involved:
The first video discusses the matter of properly escaping the SQL output, to maintain JSON integrity:
The second video introduces the Custom Functions, and illustrates their basic use
I will attach a sample file to this post which includes the functions, and various illustrations of their use, including some more esoteric advanced cases, for the inner-SQL-nerd in all of us.
Special thanks to:
JaredHague, for indulging me by taking a preview look at the videos and for a nice exchange of thoughts
rivet, for the same, and also for authoring the post that made me decide to share the CFs.
vince.menanno, for suggesting the proper parameter order for the JSON_SqlField function.
Vince -- that was the smallest change, and yet with the greatest benefit ever. Thanks!
mark_scott, for recently raising my awareness about some issues related to JSON escaping with FMP functions.
I hope you enjoy. Please feel free to direct questions to me, and I'll do my best to respond helpfully.
Update 22 March 12:30 Pacific:
I am uploading an additional demo file which has revised versions of the custom functions.
The revised demo file has a new parameter signature in the JSON_SqlSelect function.
It is named: SQL_JSON_Demo_20180322_01.fmp12.zip
There is an additional parameter in JSON_SqlSelect, which allows the developer to specify a base string of JSON to be used as a template for each row. Among other things, this feature allows for the possibility for rows to be structured as JSONArrays, rather than JSONObjects.
Many thanks to apjltd for suggesting that it would be helpful to have the ability to output rows as JSONArray structures.