Recently there have been a number of conversations about the best way to create JSON arrays from FileMaker data. A number of these have made use of executeSQL to 'gather' the data. Having played around with a number of these approaches whilst looking for the fastest techniques, I have found the biggest issue is ensuring valid JSON.
The native JSON functions take care of any encoding of special characters to ensure the JSON is valid and I believe it would be really useful if there were an option for executeSQL to perform similar encoding on the result of a SELECT.
It occurs to me that an example might be useful:
If A field contains the following
Daniela \ "Edwards" text after tab character
text after new line
ExecuteSQL with JSON encoding would return
"Daniela \\ \"Edwards\"\ttext after tab character\rtext after new line"
As it is straightforward to construct the sql SELECT statement to produce pretty much any desired JSON structure, I would not want executeSQL to return a fixed JSON structure as I would like to retain control over how the JSON is structured.