JSON Parsing Strategy

Document created by ThierryGuemboura on May 11, 2018Last modified by ThierryGuemboura on May 11, 2018
Version 4Show Document
  • View in full screen mode

I am writing this document to share my findings regarding the recent development of a "Bridge" between a proprietary REST API and FMP16.

Our community has many documents and questions pertaining to the subjects, nevertheless, when I was a newbie on the subject, I did not find a document that would describe well how to approach a daunting response made of 32,235 characters. Please writer, whom ever you are, accept my apologies if this document exists.

  1. JSON Terminology: (not my words, taken from the Internet)
    1. A JSON text is either an object, e.g. { "a" : 5 } or an array e.g. [1, 2, 3].
    2. A JSON value is either a JSON text or JSON Simple type where a JSON Simple type is either a JSON literal, a number, a string.
    3. A JSON literal is one of following three false, null or true.
    4. The literals false and true are called Booleans.
    5. A number may be either an integer or a floating-point number.
    6. There is no limit on the precision or size of numbers, but as soon as these are converted to another representation then the limitation of that representation must of course be respected, e.g. if an integer is extracted to an integer variable then the value must be between -2^63 and 2^63-1, otherwise the extraction step will produce an error.
    7. JSON strings must start and end with a double quotation mark (") and may contain any Unicode character except ", \ or control character (these characters may be escaped using \, e.g. \", \\ and \r.
  1. JSON Syntax
    1. JSON Text = JSON Object | JSON Array
    2. JSON Object = {} | { Properties }
    3. JSON Array = [] | [ items ]
    4. Properties = Property, Properties
    5. Property = String : JSON Value
    6. Items = JSON Value, Items
    7. JSON Value = JSON Text | String | Number | false | null | true
    8. String = "" | " Characters "
    9. Characters = Character Characters
    10. Character = any Unicode character except ", \ or control character | \" | \\ | \/ | \b | \f | \n | \r | \t | \u 4 hex digits
    11. Number = a number (see https://tools.ietf.org/pdf/rfc4627.pdf for a complete definition)

 

  1. FMP16 JSON functions
    1. JSONListKeys (FileMaker Pro 16 Help) ==> Format: JSONListKeys(JSON;keyOrIndexOrPath)
    2. JSONGetElement (FileMaker Pro 16 Help) ==> Format: JSONGetElement(JSON;keyOrIndexOrPath)
    3. JSONListValues (FileMaker Pro 16 Help) Format: JSONListValues(JSON;keyOrIndexOrPath)
    4. JSONFormatElements (FileMaker Pro 16 Help) ==> Format: JSONFormatElements(JSON) also known as Pretty JSON because it formats the JSON is a more human-readable format.
  1. Tool used to double-check
    1. JSON Editor Online - view, edit and format JSON online is great to see without FMP what you received.
    1. Two panes, in the left one you paste your raw JSON, the upper left icon toggle Pretty (equivalent of JSONFormatElements) or raw, the right pane deciphers the JSON
    2. Another great tool is https://www.geistinteractive.com/2018/03/13/filemaker-json-parsing-generator/
  1. Strategy of Parsing (mine which might not be the best, please comment)

The structure of any JSON reverted by an API must be analyzed to determine the structure and see what is related and what is not.

    1. Determine if JSON received is compliant (meaning has the proper syntax to be analyzed without problems). We have seen the JSON Editor Online tool above but in a script, just use:

  Set Field [Table::JSON_Pretty; JSONFormatElements ( Demo::JSON )].

if JSON cannot be parsed properly, like a missing ] or }). The JSON_Pretty field will be set with:

                               ? * Line 1, Column 1

Syntax error: value, object or array expected.

* Line 1, Column 2

  Extra non-whitespace after JSON value.

    1. Determine what type of answer was sent by the API ==> Check if Array or Object
      • Left(JSON;1) & Right(JSON; 1) = "[]" // If returns 1, it is an Array
      • Left(JSON;1) & Right(JSON; 1) = "{}" // If returns 1, it is an Object
    2. Determine how deep the highest path is:
      • ValueCount (  JSONListKeys ( JSON; "." ) ) // Returns an array of 15 objects (see the Online Editor above) for Path "." which is the highest path. I know 15 = from 0 to 14. Particularity of JSON, it is indexed from 0. The ValueCount is important as it is the max value when looping the objects. So, given the JSON sent was an Array, I knew right away that I was getting 15 times the same structure of data.
      • A second round of ValueCount (  JSONListKeys ( JSON; 0 ) ) // with this time 0 for first object returns 32. So possibly 32 "property” but will see that it is not true. We need to drill down some more.
      • Using the Online Editor shows that 3 keys are in fact arrays so more drill down
      • So, 29 Property identified which are known fields in FMP database except the "$id":"1" which is part of JSON format, no drill down needed for these.
      • The last three objects are identified as Arrays by the Online Editor, so we need to drill more, this time we have a name for each of them.
      • I did not find a solution (correct me if I am wrong), using FMP16 functions to Parse in one shot a nested array within an array so we do it in two steps.
      • First Step: I isolate the first array (one of 15 containing 32 objects) into a second work field
        • JSONGetElement( JSON ; "0" ) which reverts exactly the picture above
      • Second step: I now can drill down Claim_Contacts but knowing there are 3 arrays, we need to index the path as follows:
        • JSONGetElement(JSON_Pretty; ".Claim_Contacts[0]" )
        • Subsequent pass with Claim_Contacts[1] and Claim_Contacts[2] will parse everything
      • Same technique on Claim_Data_Prior_Claims and Claim_Notes
    1. Now that all 32 objects in one array are identified, we need to parse the content to grab the value.
    2. Again here, the free Parsing Generator by Geist Interactive is great, this generator will identify all your items (JSON wise) and will generate script steps to Set variables and fields. You can go even further and link the Generator to your real tables. I will let you discover it at the link provided above.
    3. Once you have retrieved the set variables and set field steps in a script, it is fairly easy to loop the entire JSON API answer and populate your fields.
    4. The script(s) will look something like:
  • Set Variable $MaxCount = ValueCount ( JSONListKeys ( JSON; "." ) )
  • Set Variable $ArrayCount = 0
  • Loop
    • Set $json = JSONGetElement( JSON ; $ArrayCount)
    • # Parse JSON Into Variables
    • Set Variable [ $$id; Value:JSONGetElement( $json ; "$id" ) ]
    • Set Variable [ $Claim_ID; Value:JSONGetElement( $json ; "Claim_ID" ) ]
    • Set Variable [ $Company; Value:JSONGetElement( $json ; "Company" ) ]
    • Set Variable [ $ClaimNum; Value:JSONGetElement( $json ; "ClaimNum" ) ]
    • Set Variable [ $Master_Company; Value:JSONGetElement( $json ; "Master_Company" ) ]
    • Set Variable [ $Policy_Symbol; Value:JSONGetElement( $json ; "Policy_Symbol" ) ]
    • Set Variable [ $Policy_Number; Value:JSONGetElement( $json ; "Policy_Number" ) ]
    • Set Variable [ $Policy_Module; Value:JSONGetElement( $json ; "Policy_Module" ) ]
    • Set Variable [ $Policy_Location; Value:JSONGetElement( $json ; "Policy_Location" ) ]
    • Set Variable [ $Property_Address; Value:JSONGetElement( $json ; "Property_Address" ) ]
    • Set Variable [ $Property_City; Value:JSONGetElement( $json ; "Property_City" ) ]
    • Set Variable [ $Property_State; Value:JSONGetElement( $json ; "Property_State" ) ]
    • Set Variable [ $Property_ZIP; Value:JSONGetElement( $json ; "Property_ZIP" ) ]
    • Set Variable [ $Loss_Date; Value:JSONGetElement( $json ; "Loss_Date" ) ]
    • Set Variable [ $Report_Date; Value:JSONGetElement( $json ; "Report_Date" ) ]
    • Set Variable [ $Claim_Added; Value:JSONGetElement( $json ; "Claim_Added" ) ]
    • Set Variable [ $Adjuster_Assigned; Value:JSONGetElement( $json ; "Adjuster_Assigned" ) ]
    • Set Variable [ $Claim_Accepted_Date; Value:JSONGetElement( $json ; "Claim_Accepted_Date" ) ]
    • Set Variable [ $ClAIM_ASSIGN_NOTES_DATETIME; Value:JSONGetElement( $json ; "ClAIM_ASSIGN_NOTES_DATETIME" )]
    • Set Variable [ $Loss_Type; Value:JSONGetElement( $json ; "Loss_Type" ) ]
    • Set Variable [ $Description; Value:JSONGetElement( $json ; "Description" ) ]
    • Set Variable [ $Catcode; Value:JSONGetElement( $json ; "Catcode" ) ]
    • Set Variable [ $Claim_Assignment_Status; Value:JSONGetElement( $json ; "Claim_Assignment_Status" ) ]
    • Set Variable [ $XWind; Value:JSONGetElement( $json ; "XWind" ) ]
    • Set Variable [ $Loss_Severity; Value:JSONGetElement( $json ; "Loss_Severity" ) ]
    • Set Variable [ $CLAIM_ASSIGN_NOTES; Value:JSONGetElement( $json ; "CLAIM_ASSIGN_NOTES" ) ]
    • Set Variable [ $CLAIM_ASSIGN_NOTES_USERID; Value:JSONGetElement( $json ; "CLAIM_ASSIGN_NOTES_USERID" ) ]
    • Set Variable [ $ReserveAmount; Value:JSONGetElement( $json ; "ReserveAmount" ) ]
    • Set Variable [ $ReserveChangeNotes; Value:JSONGetElement( $json ; "ReserveChangeNotes" ) ]

I wrote on purpose all my variable to show you that the job can be burdensome and it is why the Generator is a real time-saver.

  • # Set Variables Content into Fields
  • Set Field [Table::Claim_ID; $Claim_ID]
  • and so on and so forth …
  • Set Variable $ArrayCount = $ArrayCount + 1
  • Check $ArrayCount to exit loop
  • Exit Loop if $ArrayCount = $MaxCount /* $ArrayCount started at 0 therefore when equal to 15 that would be 16 loops so it’s time to exit */
  1. Same type of script to be written to deal with the 3 additional arrays, (

 

Comment: Obviously the scripts detailed above are not carved in stone, they just give a general idea of what to do.

 

Thierry Guemboura

1 person found this helpful

Attachments

Outcomes