tExtractJSONFields use expression language for extract data

[feedrss81]

Hi

I have a dynamic json, so I need to extract the data by performing a search with an expression language. The problem is that no data is returned to me. If I put on "Loop Jsonpath query" $. [? ([Attribute_code] == 'cod_cliente')] [0] and in the mapping in the Json query column I insert "value" I don't get results.

If I enter the query on the thousand pages of on-line tests, I get the desired result. is it possible then to use expression languages in Talend?

[David_Beaty]

Hi,

Can you give an example of the JSON you're reading in and a screenshot of the configured tExtractJSON component.

Thanks

David

[feedrss81]

Hi,

I uploaded the response1.json file

This is the configuration of the tExtractJSON

 

 

Thanks

Rob

[David_Beaty]

Hi,

I don't think you JSON Path Query is quite right. I achieved the result in 2 tExtractJSONFields components, the 1st one to get the 17th JSON array from the whole response, then adjusted your JSON Path Query:

Hope this helps

David

[feedrss81]

Thanks, but I can't see your possible solution.

I used the expression language because the array is dynamic.

So the value searched for some times is in 16th place, others in 17th place.

[David_Beaty]

Hi,

Yes, that response JSON is horrible. you will need to pre-process it to extract out just the array that has the key/value pair entries

Thanks

David

[feedrss81]

Hi,

Thanks, I will ask the agency to change the structure as soon as possible.

Now I have a second example.

I always need to extract a value

x [4] .Properties [0] .Val

in case

x [4] .Properties [0] .PropDefId = 79

As with the previous problem I have set up a filter

$. [? (Properties.PropDefId == 79)]. Val

It returns me an empty array.

So I didn't understand if it is possible to use JSONPath Expressions.

In case of yes I don't understand how they should be set. Or if the Loop JsonPath is to be queried in some particular way.

In the current configuration it is "$"

 

Thanks

Rob

[David_Beaty]

Hi,

I think you're going about this the wrong way - I'd suggest extracting all the column data out of the JSON structure for the key/pair JSON array and using tFilterRow or something similar to filter out the bits you want.

The reason I say this, is that you're somewhat embedding a certain amount of processing logic in the extraction of the data from the JSON.

I had a similar problem when extracting data from a Dynamics CRM, Common Data Model structure that has key/value repeating JSON objects in a large JSON array.

Thanks

David