topic Re: How to get json element based on filtering in Talend Studio

How to get json element based on filtering

Jacco_De_Zwart — Fri, 15 Nov 2024 22:19:46 GMT

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

Re: How to get json element based on filtering

Anonymous — Sat, 26 Nov 2022 00:43:33 GMT

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.

Re: How to get json element based on filtering

Jacco_De_Zwart — Mon, 28 Nov 2022 07:08:28 GMT

@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

Re: How to get json element based on filtering

Anonymous — Tue, 29 Nov 2022 00:07:22 GMT

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.

Re: How to get json element based on filtering

Jacco_De_Zwart — Wed, 30 Nov 2022 06:58:49 GMT

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

Re: How to get json element based on filtering

Anonymous — Wed, 30 Nov 2022 10:56:08 GMT

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.

Re: How to get json element based on filtering

Jacco_De_Zwart — Wed, 30 Nov 2022 11:40:31 GMT

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