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

Synopsis:

 

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.

 

 

 

Finally:

 

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

Outcomes