native json support

JSON

by Daniel Smith

Provides native support for JSON in FileMaker. It will parse JSON encoded text into a format that can easily be read via custom functions. It will also convert text encoded by custom functions into JSON.

Dependencies

#Parameters is highly recommended, although the JSON: READ ME script lists the exact custom functions required/recommended.

Credits

  • The concept for this module was proposed by Todd Geist.
  • The logic for JSON to FM conversion was copied as closely as possible from Douglas Crockford.

License

MIT License

Video

  • JSON to FM: Extract data from JSON.
  • FM to JSON: Create JSON from LetNotation, which is created via Custom Functions.

Version

Detailed version notes can be found in a script in the module, or on GitHub. I will also summarize changes here:

  • 1.0.3
    • bug fix: encode unicode control characters
  • 1.0.2
    • parse quoted number as string instead of array
    • update embedded # ( name ; value ) and #List ( value ) functions in JSON-FM
      scripts.
    • update  # ( name ; value ) and #List ( value ) custom functions
  • 1.0.1
    • fix handling of a decimal less than zero, when creating JSON
    • accommodate old version of # ( name ; value ) function that encoded numbers with GetAsNumber function
    • fix handling of Char(10)
  • 1.0.0
    • initial release

Download

Download Module

or view it on GitHub

View the “JSON: READ ME” script in the JSON module folder for integration or upgrade instructions.

44 responses to “JSON

    • Todd Geist says:

      Thats sounds like a great idea for creating JSON. I wonder if it could be extended to bringing that JSON back into FM. A good way to *create* JSON with Native FM is still missing.

      This awesome module currently just handles parsing. I shouldn’t say *just*, since parsing is probably the more difficult of the two 🙂

      • dansmith65 says:

        A good way to *create* JSON with Native FM is still missing

        Not true! I think the UI of the demo file is a little lacking, but if you click the button in the top left corner it will toggle between the FM to JSON section and JSON to FM section. There are some limitations, which you can find in the “JSON: READ ME” script, under “Known Issues”.

  1. Brian says:

    Thanks so much for sharing this!

  2. I’m struggling with the FM -> JSON part of this. What is an efficient way of building the letNotation for this?

    Thanks,
    Drew

  3. TomR303 says:

    I’d heard of JSON but don’t know much about it other than it’s supposed to be more human readable than XML. Just curious what other benefits there might be over XML? I expect this module is great if you need to access an existing service or data provided in JSON. But if I’m designing from scratch with no specific constraints would JSON be preferable to XML?

    Thanks

    • Todd Geist says:

      Hi Tom,

      Many web services are opting to you JSON as a pose to XML for communicating with APIs.

    • Justin Close says:

      Tom,
      Benefits of JSON over XML:
      * JSON tends to be more compact, less verbose
      * That tends to make it more human readable
      * It is natively readable inside Javascript without further modification. (That doesn’t mean you wouldn’t want to modify it to make it even easier to handle, just that you don’t have to.)

      There are probably more reasons, too. I am really just beginning my own experience with it.

      If I had to choose between JSON and XML data structures and it didn’t matter about interfacing with other systems, I would choose JSON. It really is very similar to associative arrays.

  4. Justin Close says:

    I don’t know if anyone else is using this, but I came up with a semi-native way of creating JSON within FileMaker. It is a bit intricate and tedious, but it works. 🙂

    You use ExecuteSQL() to pull the data, and then use SQL concatenation functions INSIDE the ESQL call itself to build the string. This is much faster than pulling the data with ESQL and then looping through the results, breaking out the elements, and then gluing them back together with string functions into a formatted JSON object. (In one instance I saved ~33% of the time taken by looping through it.)

    On rather nice affect of using ESQL is that you don’t have to track or trap for the last element. If you put a comma somewhere in the built in ‘record separator’ field of the ESQL function, then it will automatically separate the individual objects leaving the last element non-separated (i.e. no extraneous trailing comma).

    Caveat: I have only done this for a single layer deep JSON object, not something that has sub-objects/arrays nested inside of parent objects. I haven’t really thought about or tried a nested object, though; it could probably be done if you nested subquerries, too.

    Here’s my example. It uses single quotes to wrap the SQL code part of the strings to try and keep confusion down; which gets a bit hairy if you have fields that need wrapped in quotes – like some of our not-so-nice names below; also doesn’t utilize robustness-helpers like GFN or GTN…keeping it as clean as I can. It is setup of to output human readable JSON (i.e. indented and separate lines), but could easily just create a more ‘stringified’ version.


    ExecuteSQL (
    "SELECT '{¶ \"courseID\":\"' || \"__courseID\" || '\", ' ,
    ' \"coursename\":\"' || \"_CourseName\" || '\", ' ,
    ' \"segment\":\"' || Segment || '\", ' ,
    ' \"OTDate\":\"' || \"_OTDate\" || '\"¶}'
    FROM Courses
    " ; "¶ " ; ",¶" )

    — Justin

    • Justin Close says:

      Hmmm..example was much more prettily formatted/indented when I pasted it into the text box. Oh well, it isn’t too bad. And the ‘' tags usable here seems to have put odd underlines through it...

  5. Justin Close says:

    You could make a complete JSON object by putting in array notation (square brackets) around the result, or setting up a lead in notation to the result to make it a complete object or array (either of which is required to be used by inside Javascript as fully valid JSON):

    Array:
    “[” &
    ExecuteSQL (
    “SELECT ‘{¶ \”courseID\”:\”‘ || \”__MiltonID\” || ‘\”, ‘ ,
    ‘ \”coursename\”:\”‘ || \”_CourseName\” || ‘\”, ‘ ,
    ‘ \”segment\”:\”‘ || PM_Segment || ‘\”, ‘ ,
    ‘ \”OTLDate\”:\”‘ || \”_OTLReleaseDate\” || ‘\”¶}’
    FROM Courses
    ” ; “¶ ” ; “,¶” )
    & “]”

    —————-
    Object: a single element with an array as a value:

    “\”arrayObject\”:\”[\” &
    ExecuteSQL (
    “SELECT ‘{¶ \”courseID\”:\”‘ || \”__MiltonID\” || ‘\”, ‘ ,
    ‘ \”coursename\”:\”‘ || \”_CourseName\” || ‘\”, ‘ ,
    ‘ \”segment\”:\”‘ || PM_Segment || ‘\”, ‘ ,
    ‘ \”OTLDate\”:\”‘ || \”_OTLReleaseDate\” || ‘\”¶}’
    FROM Courses
    ” ; “¶ ” ; “,¶” )
    & “]}”

    • Todd Geist says:

      Looks cool Justin 🙂

    • Justin Close says:

      Hmmm…that single object version didn’t come through well. Or missed typing something. Trying again:

      " { \”arrayObject\”:\”[\" &
      ExecuteSQL (
      "SELECT '{¶ \"courseID\":\"' || \"__MiltonID\" || '\", ' ,
      ' \"coursename\":\"' || \"_CourseName\" || '\", ' ,
      ' \"segment\":\"' || PM_Segment || '\", ' ,
      ' \"OTLDate\":\"' || \"_OTLReleaseDate\" || '\"¶}'
      FROM Courses
      " ; "¶ " ; ",¶" )
      & "]}”

  6. Donald Merand says:

    I just wanted to say that I’m evaluating the new HTTP Post features of FileMaker 13, and this approach to parsing JSON comes on very handy when interfacing with web APIs from FileMaker.

    I made a HipChat client that formerly had to do a variety of AppleScript/WebViewer hacks, but which can now just use HTTP Post and your JSON library. It took me maybe 15 minutes to understand your approach. Kudos to you, and thanks very much for making this public.

    • dansmith65 says:

      Thanks for sharing how you used this module. Strangely enough, I haven’t even used this module in an application yet, so it’s nice to hear how others have used it.

  7. Chales Delfs says:

    How does this module support multiple entries in an array?
    Eg. if my JSON had a list of contact names like below:

    {
    “Contacts”: [
    {
    “First”: “John”,
    “Last”: “Smith”,
    “Age”: 23
    },
    {
    “First”: “Bill”,
    “Last”: “Jones”,
    “Age”: 42
    }
    ]
    }
    Can I find out how many entries there are so that I can loop through them and parse etc?

    • dansmith65 says:

      In the module file, if you go to JSON -> FM section, then select Sandbox, it gives you examples of how to access parsed JSON data. You can also create a new record from there to test with your own JSON. Here are some calculations that would help with your JSON:

      Let ( [
      ~data = Data::letNotation ;
      ~data = #Get ( ~data ; "Contacts" )
      ] ;
      ValueCount ( ~data )
      ) = 2

      Let ( [
      ~data = Data::letNotation ;
      ~data = #Get ( ~data ; "Contacts" ) ;
      ~data = #ListGet ( ~data ; 1 ) ;
      ~data = #GetNameList ( ~data )
      ] ;
      ~data
      ) = List ( "First" ; "Last" ; "Age")

      Let ( [
      ~data = Data::letNotation ;
      ~data = #Get ( ~data ; "Contacts" ) ;
      ~data = #ListGet ( ~data ; 1 ) ;
      ~data = #Get ( ~data ; "First" )
      ] ;
      ~data
      ) = "John"

      You can also try out Todd Geist’s #FPath function.

  8. Rutger says:

    I’m struggling (also) with the new httppost option in FileMaker Pro 13
    I try to send a JSON string, but it seems not to work.
    Need to send the following JSON (post data): {“webreg_username”:”john@stb.nl”,”webreg_password”:”a94a8fe5ccc898994c0873d391e987982fbbd3″}

    Creating the URL like::
    https://abcd.stb.nl/ws/sporf_authenticate?{“webreg_username”:”john@stb.nl”,”webreg_password”:”a94a8fe5ccc898994c0873d391e987982fbbd3″}

    seems not to work.

    Do you know how this JSON should be parsed to work with HTTPPOST?

  9. Tom Guise says:

    Just a quick comment to say that this is absolutely brilliant, I work a lot with custom web publishing and this is priceless!

  10. dansmith65 says:

    FYI: I just released a bug fix update: version 1.0.1

  11. Hi guys, of course, you won’t want the hideousness of my many custom functions, but if you really are desperate for JSON manufacture and manipulation, then my custom functions do the trick for me routinely, back and forth pretty simple. Really large data sets will fail them, but typically small data sets are perfect.

    I do think this is a symptom of a larger problem: we need to be able to handle multi-dimensional lists and dictionaries or at the very least keyed arrays within FileMaker. From that much can be achieved. But it’s a quite a serious and deep data type issue FM need to handle. Objects. Passing an object around, that’s what we are talking about. And we want functions to handle it/them. Python is a great model.

    Value Lists are better than nothing, but *so* 10 years old! 😉

    With any decent data object type JSON should come as a standard output, or would be easy to build a custom function to iterate thru the type handling it. I’d second this request, backwards compatibility is of course an issue, but new data type of JSON would circumvent problem!

  12. I’ve just completed a FileMaker – MailChimp integration using this module, and I love it. It’s saved me a lot of time trying to write JSON manually.

    Thank you!

    • dansmith65 says:

      Your welcome! I’m glad it worked out for you. It’s nice to hear that people are finding it useful.

  13. Luc Roels says:

    First off:
    Thanks for creating and posting this.

    One minor remark:
    You might change just one line at the end of the subscript “~JSON-FM Number”:
    Set Variable [$number; GetAsNumber ( $string )]
    should be
    Set Variable [$number; GetAsNumber ( Substitute ( $string; “.”; char.decimalseparator ) )]
    where
    char.decimalseparator is a custom function [ Left ( 1/2 ; 1 ) ] that always returns the correct decimal separator, wherever you are (i.e. Belgium, France, etc…)

    Thanks for sharing and the whole concept of Modular FileMaker.

    Luc

    • dansmith65 says:

      Thanks for mentioning this; I forgot to work this into the current bug fix update; I’ll try to remember to do it next time I post an update.

  14. dansmith65 says:

    FYI: I just posted a bug fix update. You’ll only need this if your JSON contains unicode control characters. It’s easy to update: just replace all steps in script: ~FM-JSON { string }

  15. […] a plethora of programming languages and databases. While not listed on the page there is also a JSON module for FileMaker.  For mySQL there is a discussion of a couple ways of formatting the output of a mySQL query into […]

  16. Jim Randell says:

    I just wanted to take a minute to tell you how impressed I am with this module and its logic. I do a lot of work with APIs and thus with json. I have been working on this sort of think for a long time. But, in all my iterations and implementations, I have not been able to build anything as clean, elegant, and SOLID, as what you have done here.

    So far, its taken everything I have thrown at it.
    Thank you for sharing.

    -Jim

  17. Dan Weiss says:

    Thanks so much for this tool !

    We are having trouble parsing salesforce JSON. Here’s an example:

    $responseText = “{\”totalSize\”:1,\”done\”:true,\”records\”:[{\”attributes\”:{\”type\”:\”Account\”,\”url\”:\”/services/data/v32.0/sobjects/Account/001L000000hdu4gIAA\”},\”Name\”:\”Adatasol Test\”,\”Baan_Customer_Number__c\”:\”987654321\”,\”Line_Of_Business__c\”:\”111-User – Cranes Dockside\”}]}” ;

    Using Let ( [
    ~data = Data::letNotation;
    ~data = #Get (~data ; “responsetext”)

    ] ;
    ~data
    )

    Get’s the value of the whole responsetext but then I’m not sure how to parse the sub-values.

    Any help appreciated.

    • Daniel Smith says:

      The value returned by your example code above is JSON and needs to be converted to let notation before you can read it with this module. Take that JSON and send it to the JSON-FM script (or paste it into a new record in the JSON -> FM Sandbox), then you can extract data from the resulting Let Notation:


      Let ( [
      ~data = Data::letNotation ;
      ~records = #Get ( ~data ; "records" ) ;
      ~record = #ListGet ( ~records ; 1 )
      ] ;
      #Get ( ~record ; "Name" ) = "Adatasol Test"
      and #Get ( ~record ; "Baan_Customer_Number__c" ) = 987654321
      )

  18. Dan Weiss says:

    I feel stupid but the code you’ve provided results in a 0 in the sandbox. Is that the correct result? Our goal is to be able to pull out the Name or Baan Customer number.

    Here’s another example, this time using the contact vs. account in Salesforce. Again, tried for hours to get this to parse without luck.

    {“onabort”:null,”onloadend”:null,”onloadstart”:null,”onprogress”:null,”ontimeout”:null,”onreadystatechange”:null,”timeout”:0,”readyState”:4,”withCredentials”:false,”upload”:{“onabort”:null,”onerror”:null,”onload”:null,”onloadend”:null,”onloadstart”:null,”onprogress”:null},”responseText”:”{\”totalSize\”:1,\”done\”:true,\”records\”:[{\”attributes\”:{\”type\”:\”Contact\”,\”url\”:\”/services/data/v32.0/sobjects/Contact/003L000000b1UNzIAM\”},\”Account\”:{\”attributes\”:{\”type\”:\”Account\”,\”url\”:\”/services/data/v32.0/sobjects/Account/001L000000heKiZIAU\”},\”Id\”:\”001L000000heKiZIAU\”},\”Id\”:\”003L000000b1UNzIAM\”,\”FirstName\”:\”Daniel\”,\”LastName\”:\”Weiss\”}]}”,”responseXML”:null,”responseType”:””,”response”:”{\”totalSize\”:1,\”done\”:true,\”records\”:[{\”attributes\”:{\”type\”:\”Contact\”,\”url\”:\”/services/data/v32.0/sobjects/Contact/003L000000b1UNzIAM\”},\”Account\”:{\”attributes\”:{\”type\”:\”Account\”,\”url\”:\”/services/data/v32.0/sobjects/Account/001L000000heKiZIAU\”},\”Id\”:\”001L000000heKiZIAU\”},\”Id\”:\”003L000000b1UNzIAM\”,\”FirstName\”:\”Daniel\”,\”LastName\”:\”Weiss\”}]}”,”status”:200,”statusText”:”OK”}

    • Daniel Smith says:

      For some reason, responseText is encoded as a string, so it’s not json yet. Once you convert the json above to let notation, you’ll have to extract responseText via #Get ( $dataFromYourPost ; “responseTest”). That will give you jason that you have to send back to the JSON module to convert it to Let Notation.

      I’m not sure why the data was encoded that way; is this a raw response from Salesforce, or do you have some other process that is re-encoding the response and adding data to it?

  19. I am having a heck of a time figuring out how to pass the proper JSON string in a URL. When I put the string in the body using Postman, it does return the proper response. When I try to do it via Filemaker and Insert from URL, no dice. Here is the format I need to send, which seems simple enough of the surface:

    {
    “opportunities”:[
    {
    “AccountName”:”Clark Capital Management Group, Inc.”,
    “Name”:”Babe Ruth Proposal”,
    “StageName”:”Proposal Delivered”,
    “CloseDate”:”11/18/2015″,
    “Amount”:”1000000.00”,
    “ClosedAmount”:”800000.00”
    }
    ]
    }

    Cannot figure out why this isn’t working. I know I must be doing something dumb, but I’m having a difficult time researching my way outta this one. Can anyone give me a clue?

    • Daniel Smith says:

      FileMaker does some funky stuff with Insert From URL, if you’re using httppost: http://www.restfm.com/restfm-manual/web-api-reference-documentation/submitting-data/filemaker-progo-insert-url-httppost. Maybe your issue is related to that?

      I’d suggest trying the same request with a plugin, as a method of debugging the issue. I like using https://httpbin.org/ when testing http requests.

      • This whole thing is giving me an “above my pay grade” headache. This stuff is so far over my head it must be on Mars. 🙂 I am indeed using httppost, but there are no “%” or “=” in the submission, other than the %20 space characters added after the fact. Haven’t a clue what to do with the httpbin site. But thank you for trying to educate the ignorant. Feeling really dopey right about now. I’ve tried the submission using the Base Elements plug in (BE_HTTP_POST). Same result. Hopefully the API developer will be able to shed some light on this.

        • Spoke with the API developer, and apparently this is the relevant issue:

          Note: Under the hood, ‘httppost’ POSTs data as application/x-www-form-urlencoded format. It is not possible to submit a raw message body, the data must be passed in as ‘fieldName=value’ pairs separated by ‘&’.

          The ability to compose and transmit a raw message body constructed of a JSON encoded payload is what the API is after. He is going to slightly adjust his code and I am going to parse the string to use ASCII codes rather than things like spaces, {, [, etc. He thinks that will do the trick.

          Never easy!

Leave a Reply

Your email address will not be published. Required fields are marked *