Strange tExtractJsonFields behaviour

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
Highlighted
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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads