How to get json element based on filtering

[Jacco_De_Zwart]

Hi there,

When I send a request to Google places API, I get the following json result :

  "html_attributions" : [],

  "result" : {

   "address_components" : [

     {

      "long_name" : "55/1",

      "short_name" : "55/1",

      "types" : [ "street_number" ]

     },

     {

      "long_name" : "Heidenheimer Straße",

      "short_name" : "Heidenheimer Str.",

      "types" : [ "route" ]

     },

     {

      "long_name" : "Ulm",

      "short_name" : "Ulm",

      "types" : [ "locality", "political" ]

     },

     {

      "long_name" : "Kreisfreie Stadt Ulm",

      "short_name" : "Kreisfreie Stadt Ulm",

      "types" : [ "administrative_area_level_3", "political" ]

     },

     {

      "long_name" : "Tübingen",

      "short_name" : "TÜ",

      "types" : [ "administrative_area_level_2", "political" ]

     },

     {

      "long_name" : "Baden-Württemberg",

      "short_name" : "BW",

      "types" : [ "administrative_area_level_1", "political" ]

     },

     {

      "long_name" : "Germany",

      "short_name" : "DE",

      "types" : [ "country", "political" ]

     },

     {

      "long_name" : "89075",

      "short_name" : "89075",

      "types" : [ "postal_code" ]

     }

   ]

  },

  "status" : "OK"

}

next step is that I use tExtactJSONFields to get the individual elements.

When I copy the result in jsonpath.com to see how to get the elements, I can get each individual element by using :

$..address_components[?(@.types.indexOf('street_number') != -1)].[long_name]

and so on

in the tExtractJSONField it looks like this :

and in the advanced settings 'Use the loop node as root' is checked.

When I run the job i get the error

Could not compile inline filter : [?(@.types.indexOf('street_number') != -1)]

So two questions :

• What is wrong with this code ? It works fine in jsonpath.com
• What other way is there to get the individual elements ?

Thanks in advance

Jacco

[Anonymous]

There are different implementations of JSONPath unfortunately. At the moment "indexOf" is not supported in the libraries we are using. However, this can resolved with a slightly different syntax. I have tested this and it works with your example JSON.

First, your loop path will need to be ....

"$.result"

This will prevent the component from looping over the array.

Then your JSONPath queries will need to be in this format....

"address_components[?('street_number' in @.types)].long_name"

"address_components[?('route' in @.types)].long_name"

You'll see that the two examples are essentially the same, just with different search criteria of "street_number" and "route".

I have tested this and it works as I believe you want it.

[Jacco_De_Zwart]

@Richard Hall​ 

Thanks for your reply !

I rewrite my to according to your suggestion and it works.

 

The only thing that I have now is that the result looks like (in tlog output):

["3"]|["Telitie"]|["Hyrylä"]|["04300"]|[]|["FI"]

["2"]|["Tavernierkaai"]|["Antwerpen"]|["2000"]|["Vlaams Gewest"]|["BE"]

["8"]|["Im Stadtfelde"]|["Wunstorf"]|["31515"]|["Niedersachsen"]|["DE"]

["3"]|["Brookstieg"]|["Stapelfeld"]|["22145"]|["Schleswig-Holstein"]|["DE"]

["700"]|["Churchilllaan"]|["Terneuzen"]|["4532 JB"]|["Zeeland"]|["NL"]

["29"]|["Baindter Straße"]|["Baienfurt"]|["88255"]|["Baden-Württemberg"]|["DE"]

Why are there still the brackets [ and ] and the dobble quotes in the result ??

Jacco

[Anonymous]

OK, this is a little trickier. But it can be done. In the next component, you will need to process each column using the following code using regex.....

row.column.replaceAll("((^\\[\")|(\"\\]$))|((^\\[)|(\\]$))", "")

Copy this exactly. What it does is it looks for values with [" and "] at the beginning and end of the String and also looks for just [ and ] at the beginning and end of the String. This will deal with that issue.

[Jacco_De_Zwart]

Thanks for the answer ! Tried it and it works (off course)

But why does the call to Google api https://maps.googleapis.com/maps/api/place/findplacefromtext return me a placeid, which I can extract like this :

 

and this result doesn't have the "[.......]" characters surrounding the value of the field place_id

Is there a difference ??

Jacco

[Anonymous · Accepted solution]

This is because you are selecting a single item from an individual array element. You are using the Loop JSONPath query to iterate over the array elements in the image above. In the example I gave you (based on your initial attempt to get the data), you are selecting from the whole array. As such, the record is returned as an array as it *could* match more than one value.

[Jacco_De_Zwart]

Thanks again. I will experiment a bit more with this.