Parsing JSON with challenging "dot" keys

Document created by steve_ssh on Sep 20, 2018Last modified by steve_ssh on Sep 23, 2018
Version 10Show Document
  • View in full screen mode



Last week I saw this post, which concerns the topic of parsing JSON which uses dot ( "." ) characters within its keys.

This document is home to a pair of custom functions which I wrote to address the scenario described in the above post.




Example of JSON With Dots in Key Names:


    {  "comment.100": "This should not be done", "comment.200": "An array would be much more appropriate." }


It is mentioned in the comments of the post, that it would be best to avoid using such keys in the first place.  I completely agree.


That said, for cases where there is no hope for changing the structure and the keys, I decided that it might be nice to have a pair of custom functions available to help ease the inconvenience of reading and updating such JSON.




The Custom Functions:


Custom functions JSONGetElementDots and  JSONSetElementDots  are analogous to their native FMP counterparts:


json dot helper cfs.png

Two important distinctions between these functions, and the native FMP functions:


   • JSONSetElementDots does not allow setting multiple values in a single function call

   • JSONGetElementDots always returns a string value.  If your solution is expecting a numeric value, please wrap the output in GetAsNumber.



The benefit of these functions:


The path argument for each of these functions allows for dot characters to be backslash-escaped.


A dot character in a path which is preceded by a backslash will not be interpreted as a path delimiter, and instead will be interpreted as a character within the JSON key name.  It is this feature which affords a reasonable workaround for reading and updating JSON which uses dot characters within key names.


dot helper example.png


Implementation Notes:


The basic strategy:


    • Escape all dot characters in the JSON

    • Perform the Get/Set action on the escaped data, using the native FileMaker JSON functions.

    • Undo any effects of the escaping performed in the first step.



Special Care for JSON Number Values:


A day after I posted the initial version of one of these custom functions, I realized that I had not taken proper care to avoid breakdowns in cases where escaping a dot character could invalidate a JSON numeric value which is either fractional, or expressed in scientific notation.  I have since re-worked the function to handle these considerations.






I hope that these may be helpful for any solutions which find it necessary to parse or manipulate JSON that has keys containing dot characters.


Thank you for taking a look.


3 people found this helpful