topic Re: Extracting from BLOB column in Talend Studio

Extracting from BLOB column

Anonymous — Tue, 27 Nov 2018 04:48:30 GMT

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

Re: Extracting from BLOB column

vapukov — Tue, 27 Nov 2018 12:16:23 GMT

what is your database?

Re: Extracting from BLOB column

Anonymous — Wed, 28 Nov 2018 05:11:56 GMT

Its a MySQL Database..

Re: Extracting from BLOB column

vapukov — Thu, 29 Nov 2018 06:30:45 GMT

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

Re: Extracting from BLOB column

apz — Sat, 15 Feb 2020 13:39:39 GMT

Do you have the screenshot of your tMap settings.