Strange tExtractJsonFields behaviour

Highlighted
Four Stars

Strange tExtractJsonFields behaviour

Hi, I have JSON document stored in DB table and I need to extract those data in order to write them on MySQL. Here's an example of a JSON field I want to parse:

 

[{"val":"0","slotId":"T1","slotType":"ABS"},{"val":"0","slotId":"T2","slotType":"ABS"},{"val":"0","slotId":"T3","slotType":"ABS"},{"val":"0","slotId":"T4","slotType":"ABS"},{"val":"0","slotId":"TOT","slotType":"ABS"}]

and here's my tExtractJSONFields configuration:Immagine.jpg

Everything semmed to work but results are not like expected. In particular what I want is all same pod values on the same row, I got this instead:

Immagine2.jpg

 

What am I missing?

Nine Stars

Re: Strange tExtractJsonFields behaviour

Hi Are you looking for the following output?

Testjson1.PNGTestjson2.PNGTestjson3.PNGTestjson4.PNG

Regards,

Veeru Boppudi
Four Stars

Re: Strange tExtractJsonFields behaviour

Hi, Thank you for your reponse! I see you've set max function in tAggregateRow and this is not what I want.... suppose I've a DB with million of JSON documents like the one I've posted before and for each of them I need a row with all "Tx" fields (plus an ID field in order to join with other tables). I tried to replicate your configuration on my sample dataset which contains just 5 json entries, 2 of them lack of "T4" field. My sample dataset look like this:

Immagine.jpg

 

And this is what I expect to see:

 Immagine2.jpg

 

 

 

Thank you for your help!

Nine Stars

Re: Strange tExtractJsonFields behaviour

Please provide data in file if possible.

 

Regards,

Veeru Boppudi
Nine Stars

Re: Strange tExtractJsonFields behaviour

please try below

Testjson1.PNGTestjson2.PNGTestjson3.PNGTestjson4.PNG

Regards,

Veeru Boppudi
Four Stars

Re: Strange tExtractJsonFields behaviour

here's the requested sample Smiley Happy

Nine Stars

Re: Strange tExtractJsonFields behaviour

Hi,

 

please follow the new approach.

Testjson1.PNG

Regards,

Veeru Boppudi
Four Stars

Re: Strange tExtractJsonFields behaviour

Cool, much better, pretty close... btw if you look at desired table posted above, I have two NULL values in T4 column due to missing field in corresponding jsons and I really need to keep this information. I tried to substitute zero in row2.slotId.equals("T2")?Float.parseFloat(row2.val):0  with null or even leaving it empty (I slightly modified your code since I want my data to be float type) but talend returns an error

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch