JSON GET FUNCTION

[Jeffrey Bloch]

The following JSON result is different than I'm used to seeing. I'm trying to access COUNTRY but the term 'Country' exists only below the data itself under a subheading "types."

The following statement doesn't get the COUNTRY data for me. Any thoughts?

JSONGetElement ( $$placeResult ; "result.address_components.country" ) nor does

JSONGetElement ( $$placeResult ; "result.address_components.country.long_name" )

FYI - I can't simply use: JSONGetElement ( $$placeResult ; "result.address_components[3]" ) as the number of elements differ based upon the search.

{
   "html_attributions" : [],
   "result" :
   {
      "address_components" :
      [
         {
            "long_name" : "195",
            "short_name" : "195",
            "types" :
            [
               "street_number"
            ]
         },
         {
            "long_name" : "Woodrow Avenue",
            "short_name" : "Woodrow Ave",
            "types" :
            [
               "route"
            ]
         },
         {
            "long_name" : "Southport",
            "short_name" : "Southport",
            "types" :
            [
               "neighborhood",
               "political"
            ]
         },
         {
            "long_name" : "Fairfield",
            "short_name" : "Fairfield",
            "types" :
            [
               "locality",
               "political"
            ]
         },
         {
            "long_name" : "Fairfield County",
            "short_name" : "Fairfield County",
            "types" :
            [
               "administrative_area_level_2",
               "political"
            ]
         },
         {
            "long_name" : "Connecticut",
            "short_name" : "CT",
            "types" :
            [
               "administrative_area_level_1",
               "political"
            ]
         },
         {
            "long_name" : "United States",
            "short_name" : "US",
            "types" :
            [
               "country",
               "political"
            ]
         },
         {
            "long_name" : "06890",
            "short_name" : "06890",
            "types" :
            [
               "postal_code"
            ]
         },
         {
            "long_name" : "1121",
            "short_name" : "1121",
            "types" :
            [
               "postal_code_suffix"
            ]
         }
      ],
      "adr_address" : "\u003cspan class=\"street-address\"\u003e195 Woodrow Ave\u003c/span\u003e, \u003cspan class=\"locality\"\u003eSouthport\u003c/span\u003e, \u003cspan class=\"region\"\u003eCT\u003c/span\u003e \u003cspan class=\"postal-code\"\u003e06890-1121\u003c/span\u003e, \u003cspan class=\"country-name\"\u003eUSA\u003c/span\u003e",
      "formatted_address" : "195 Woodrow Ave, Southport, CT 06890, USA",
      "geometry" :
      {
         "location" :
         {
            "lat" : 41.1409221,
            "lng" : -73.28849129999999
         },
         "viewport" :
         {
            "northeast" :
            {
               "lat" : 41.1422797302915,
               "lng" : -73.28703961970848
            },
            "southwest" :
            {
               "lat" : 41.1395817697085,
               "lng" : -73.28973758029149
            }
         }
      },
      "icon" : "https://maps.gstatic.com/mapfiles/place_api/icons/v1/png_71/geocode-71.png",
      "icon_background_color" : "#7B9EB0",
      "icon_mask_base_uri" : "https://maps.gstatic.com/mapfiles/place_api/icons/v2/generic_pinlet",
      "name" : "195 Woodrow Ave",
      "place_id" : "ChIJjdXiqpAa6IkR4ol3Zgd0QEY",
      "reference" : "ChIJjdXiqpAa6IkR4ol3Zgd0QEY",
      "types" :
      [
         "premise"
      ],
      "url" : "https://maps.google.com/?q=195+Woodrow+Ave,+Southport,+CT+06890,+USA&ftid=0x89e81a90aae2d58d:0x46407407667789e2",
      "utc_offset" : -300,
      "vicinity" : "Fairfield"
   },
   "status" : "OK"
}

Edited January 21 by Jeffrey Bloch

[comment]

The address_components element in your JSON contains an array. You must specify the index number of the array item you wish to access. In your example, the expression:

JSONGetElement ( $yourJSON ; "result.address_components[6]long_name" )

will return "United States" (note that the index number is [6] and not [3] as you state).

The JSON functions provided in Filemaker do not allow you to query the array to find an item by its value. If you don't know in advance the index number of the item you need, you will need to do one of the following:

• loop over the items until you find a match; or:
• use another language (e.g. Javascript in a web viewer) to parse the JSON.

 

[Jeffrey Bloch]

Thanks for the reply. The array isn’t predictable (ie country = 6) therefore I need a way to programmatically GET the data based upon the “types” field below it. I could do a comprehensive CALC using POSITION and other functions, but I imagine there have to be JSON functions (hopefully native) which can do what I need. 

[comment]

1 hour ago, Jeffrey Bloch said:

I imagine there have to be JSON functions (hopefully native) which can do what I need. 

4 hours ago, comment said:

The JSON functions provided in Filemaker do not allow you to query the array to find an item by its value.

Are you doing this in a script?

 

[Jeffrey Bloch]

I previously used a script which looped based upon the number of items in the response and assigned them to fields as part of the process. I’m trying to tighten up processes to reduce lags by utilizing calc fields to GET the data dynamically rather than relying on scripts/loops. 

Edited January 21 by Jeffrey Bloch

[comment]

You cannot get the data you want without looping (unless you want to use another language as I explained earlier). If you already have a script that fetches the JSON (as I would imagine), it would be convenient to utilize a looping script to extract the details from it. Otherwise you would need to use the While() function or a recursive custom function in a calculation field. I believe best practice would keep the entire logic in a single script (or possibly with a subscript) rather than split it into two separate locations (three, if a custom function is involved).

 

[Jeffrey Bloch]

Thanks. No doubt you’re right, but it’s amazing to me that a response from Google (Places) wouldn’t be formatted in such a way that one could easily grab embedded data with a single statement. Any thought if perhaps there could be a setting on the Google end which opts for an easier response to handle? I don’t expect you to know Google off the top of your head, but maybe you’ve seen something similar in your time. 

[comment]

I have seen people looking for a Javascript solution to the same problem. Here's just one example:
https://stackoverflow.com/questions/21727931/json-result-get-country-from-address-components

 

[Jeffrey Bloch]

Thanks! You never disappoint. 

[Jeffrey Bloch]

I know I said I prefer native functionality, but since this didn't appear possible, I went in search of the best custom function solution and want to post it here for future onlookers.

Thanks to Brian Dunning and Alex Zueiv (https://www.briandunning.com/cf/2617)!

jsonFindValue ( address.google.placeResult ; "country" ) produces the array key I need for the JSONGetElement statement.

CALC STATEMENT:

Let (

[
jsonData = address.google.placeResult ;
textSearch = "country" ;
textFound = jsonFindValue ( jsonData ; textSearch ) ;
trimPosition = Position ( textFound ; "." ; 1 ; 3 ) -1 ;
trimmedText = Middle ( textFound ; 2 ; trimPosition ) & "long_name"
]

;

Proper ( JSONGetElement ( jsonData ; trimmedText ) )

)

Edited January 22 by Jeffrey Bloch

[comment]

41 minutes ago, Jeffrey Bloch said:

I said I prefer native functionality, but since this didn't appear possible

It is definitely possible and the custom function you refer to does exactly that - and it does it by looping over all the values in the given JSON.

As such, it has 2 disadvantages when applied to your situation:

1. It will find and return the path to any element whose value is "country", regardless of where it appears in the given JSON;
2. It will waste time and processing power by going over the entire JSON, instead of searching only within the  address_components array. This contradicts your stated purpose:

   14 hours ago, Jeffrey Bloch said:

   I’m trying to tighten up processes to reduce lags

 

BTW, I wonder if Google still provide an option to get an XML response instead of JSON. That would be much easier to consume, since XML can be imported directly (with the help of an XSLT stylesheet to transform it to FMP's own XML grammar).

 

[Jeffrey Bloch]

57 minutes ago, comment said:

This contradicts your stated purpose:

Great point(s). I’ll narrow the portion of the JSON as I’ve already retrofitted. One other goal I didn’t mention is to minimize the time I’ll need to troubleshoot issues and reducing dependency on scripts (where possible without too much overhead) and the requisite back-and-forth simplifies the future. Unfortunately, it’s often a crapshoot for me to find the best methods as I’m not so readily aware of the overhead costs to be making the best decisions at all times. Yet another reason I post to this forum. 
I’ll look into the XML option. 

1 hour ago, comment said:

BTW, I wonder if Google still provide an option to get an XML response instead of JSON. That would be much easier to consume, since XML can be imported directly (with the help of an XSLT stylesheet to transform it to FMP's own XML grammar).

Turns out it's just as easy as changing "json" to "xml."

"https://maps.googleapis.com/maps/api/place/details/xml?placeid=" & $place_ID &  "&key=" & SYSTEM_SETTINGS | INCIDENTS | ALL::GOOGLE API KEY

[comment]

17 minutes ago, Jeffrey Bloch said:

it's just as easy as changing "json" to "xml."

So what does the resulting XML response look like?

 

[Jeffrey Bloch]

3 minutes ago, comment said:

So what does the resulting XML response look like?

<?xml version="1.0" encoding="UTF-8"?>
<PlaceDetailsResponse>
 <status>OK</status>
 <result>
  <name>1600 Pennsylvania Avenue NW</name>
  <vicinity>Washington</vicinity>
  <type>street_address</type>
  <formatted_address>1600 Pennsylvania Avenue NW, Washington, DC 20500, USA</formatted_address>
  <address_component>
   <long_name>1600</long_name>
   <short_name>1600</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Pennsylvania Avenue Northwest</long_name>
   <short_name>Pennsylvania Avenue NW</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Northwest Washington</long_name>
   <short_name>Northwest Washington</short_name>
   <type>neighborhood</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Washington</long_name>
   <short_name>Washington</short_name>
   <type>locality</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>District of Columbia</long_name>
   <short_name>DC</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>United States</long_name>
   <short_name>US</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>20500</long_name>
   <short_name>20500</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>38.8976801</lat>
    <lng>-77.0363304</lng>
   </location>
   <viewport>
    <southwest>
     <lat>38.8962674</lat>
     <lng>-77.0374228</lng>
    </southwest>
    <northeast>
     <lat>38.9015474</lat>
     <lng>-77.0343435</lng>
    </northeast>
   </viewport>
  </geometry>
  <url>https://maps.google.com/?q=1600+Pennsylvania+Avenue+NW,+Washington,+DC+20500,+USA&amp;ftid=0x89b7b78e00410e73:0xac8125363999a8f</url>
  <icon>https://maps.gstatic.com/mapfiles/place_api/icons/v1/png_71/geocode-71.png</icon>
  <reference>ChIJcw5BAI63t4kRj5qZY1MSyAo</reference>
  <utc_offset>-300</utc_offset>
  <adr_address>&lt;span class=&quot;street-address&quot;&gt;1600 Pennsylvania Avenue NW&lt;/span&gt;, &lt;span class=&quot;locality&quot;&gt;Washington&lt;/span&gt;, &lt;span class=&quot;region&quot;&gt;DC&lt;/span&gt; &lt;span class=&quot;postal-code&quot;&gt;20500&lt;/span&gt;, &lt;span class=&quot;country-name&quot;&gt;USA&lt;/span&gt;</adr_address>
  <place_id>ChIJcw5BAI63t4kRj5qZY1MSyAo</place_id>
  <plus_code>
   <global_code>87C4VXX7+3F</global_code>
   <compound_code>VXX7+3F Washington, DC, USA</compound_code>
  </plus_code>
  <icon_background_color>#7B9EB0</icon_background_color>
  <icon_mask_base_uri>https://maps.gstatic.com/mapfiles/place_api/icons/v2/generic_pinlet</icon_mask_base_uri>
 </result>
</PlaceDetailsResponse>

 

[comment]

Here is an XSLT stylesheet that will allow you to import directly 6 fields I picked arbitrarily (you can easily add more):

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:key name="addr_compt-by-type" match="address_component" use="type" />

<xsl:template match="/PlaceDetailsResponse">
   <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
      <!-- FIELDS -->
      <METADATA>
         <FIELD NAME="Name"/>
         <FIELD NAME="StreetNumber"/>
         <FIELD NAME="Route"/>
         <FIELD NAME="Locality"/>
         <FIELD NAME="Country"/>
         <FIELD NAME="ZIP"/>
      </METADATA>
      <!-- DATA -->
      <RESULTSET>
         <ROW>
            <COL>
               <DATA>
                  <xsl:value-of select="result/name"/>
               </DATA>
            </COL>
            <COL>
               <DATA>
                  <xsl:value-of select="key('addr_compt-by-type', 'street_number')/long_name"/>
               </DATA>
            </COL>
            <COL>
               <DATA>
                  <xsl:value-of select="key('addr_compt-by-type', 'route')/long_name"/>
               </DATA>
            </COL>
            <COL>
               <DATA>
                  <xsl:value-of select="key('addr_compt-by-type', 'locality')/long_name"/>
               </DATA>
            </COL>
            <COL>
               <DATA>
                  <xsl:value-of select="key('addr_compt-by-type', 'country')/long_name"/>
               </DATA>
            </COL>
            <COL>
               <DATA>
                  <xsl:value-of select="key('addr_compt-by-type', 'postal_code')/long_name"/>
               </DATA>
            </COL>
         </ROW>
      </RESULTSET>
   </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

 

[Jeffrey Bloch]

20 minutes ago, comment said:

Here is an XSLT stylesheet that will allow you to import directly

Thanks so much! I’ve not yet used stylesheets so I’ll have to study up on it. 

[comment]

3 minutes ago, Jeffrey Bloch said:

I’ll have to study up on it. 

Highly recommended - probably the most under-utilized power feature of Filemaker.

(And could be so much more powerful if Claris would upgrade the ancient XSLT 1.0 processor to a modern version.)

 

[Jeffrey Bloch]

2 minutes ago, comment said:

Highly recommended - probably the most under-utilized power feature of Filemaker.

Will do. 

[Ocean West]

you might be able to do a query with this custom function 

 https://filemakerhacks.com/2022/01/13/jsonquery-at-fm-disc/

[Jeffrey Bloch]

10 minutes ago, Ocean West said:

you might be able to do a query with this custom function 

Thanks!