Option for executeSQL to return JSON encoded data

Idea created by Paul Jansen on Jun 25, 2018
    Active
    Score12


    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.

     

    For example

    [

    {"firstName":"Arthur","lastName":"Dent"},

    {"firstName":"Ford","lastName":"Prefect"}

    ]

    or

    [

    {"person":{"firstName":"Arthur","lastName":"Dent"}},

    {"person":{"firstName":"Ford","lastName":"Prefect"}}

    ]

    or

    [

    ["Arthur","Dent"],

    ["Ford","Prefect"]

    ]