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?

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