Extracting from BLOB column

[Anonymous]

I have one BLOB column and FId column in Table1. In BLOB column, each row contains JSON data in the following format,

{
"Flowers" : {
"Flower1" : "Jasmine",
"Flower2" : "Rose",
"Flower3" : "Lilly"
},
"Fruits" : {
"Fruit1" : "Apple",
"Fruit2" : "Orange",
"Fruit3" : "Strawberry"
},
"Vegetables" : {
"Vegetable1" : "Cucumber",
"Vegetable2" : "Brinjal",
"Vegetable3" : "Potato"
}
}

I want to extract the Fruits section in to each column in Table2..

Eg:- FId    Fruit1     Fruit2      Fruit3
-----------------------------------------------
        1       Apple     Orange     Strawberry

How can I extract only one section of the JSON in to different columns?? Also I want the FId value corresponding to each JSON data in  Table1 to Table2..

Thanks In Advance..

[vapukov]

what is your database?

[Anonymous]

Its a MySQL Database..

[vapukov]

simplest ways use 2 components, one from exchange :

tJsonNormalize (author Jan Lolling)

second from Talend - tPivotToColumnsDelimited

extract JSON from BLOB as Text, like

select convert(json_blob using utf8) as json_blob from

in tDBInput type for column - String

you result will be:

add to tMap - filter, and exclude JSON_PATH from tMap output, it will be exactly what you want

[apz]

Do you have the screenshot of your tMap settings.