AnsweredAssumed Answered

Extract JSON from nested arrays

Question asked by osninc on Sep 5, 2017
Latest reply on Mar 29, 2018 by user19752

Hi,

 

I am trying to learn how to import JSON that has an array structure into new records in a related table.  I have a table named PROPERTY and one named TRANSACTIONS.  In the PROPERTY table, I have a field named JSON that contains the return resulan of a API call where it stores the JSON.  I need to parse it but it is a fairly complicated structure with nested arrays. The number of occcurance in the array varries from one record to the next but always has at least a 0 and 1 but may from 0-N but I only want to extract what is in array 1-N.  I know the path I want is the following:

     

RESPONSE_GROUP.RESPONSE.RESPONSE_DATA.PROPERTY_INFORMATION_RESPONSE_ext.SUBJECT_PROPERTY_ext.PROPERTY.[1-N].SALES_HISTORY.PropertySalesAmount

   Inside of each

RESPONSE_GROUP.RESPONSE.RESPONSE_DATA.PROPERTY_INFORMATION_RESPONSE_ext.SUBJECT_PROPERTY_ext.PROPERTY.[1-N].SALES_HISTORY.LOAN_ext.[0-3].LenderLastName

 

Can someone please help explain how you would write a script to recursively extract the paths above and create new records in the related table?

I found this on the Filemaker site but cant seem to get it to work with the JSON in my solution: 

The following example script determines the number of products in the Example JSON data (stored in the $$JSON variable) using JSONListKeys, creates a record for each product, then sets fields in each record to the values obtained using JSONGetElement for each product.

Set Variable [ $ProductCount ; Value:
   ValueCount (
      JSONListKeys ( $$JSON ; "bakery.product" )
   ) ]
Set Variable [ $i; Value: 0 ]
If [ $ProductCount > 0 ]
   Loop
      New Record/Request
      Set Field [ Products::ID ;
         JSONGetElement ( $$JSON ; "bakery.product[" & $i & "]id" ) ]
      Set Field [ Products::Price ;
         JSONGetElement ( $$JSON ; "bakery.product[" & $i & "]price" ) ]
      Set Field [ Products::Stock ;
         JSONGetElement ( $$JSON ; "bakery.product[" & $i & "]stock" ) ]
      Commit Records/Requests [ With dialog: Off ]
      Set Variable [ $i ; Value: $i + 1 ]
      Exit Loop If [ $i $ProductCount ]
   End Loop
End If

 

I have saved the JSON structure in my solution to this JSONMate - JSON editor, inspector and beautifier and have also pasted an image below:

 

Thanks

JSON.png

Outcomes