how to convert delimited values to json array and key value pair

[Bharath12]

Hi All,

I have issues with converting delimited values  to JSON array. can some one help me with the below requirements.

Requirement 1:

source from mysql DB:

id

123,456,7458,4566897,32132164

I need JSON output like below.

"ID":["123","456","7458","4566897","32132164"]

Requirement 2: 

source data

work email                                                 Alias

12345@gmail.com                        xyz@gmail.com,abc@gmail.com,cyd@gmail.com

i need output like below

"email" : [ {"type" : "workemail", "value" : "12345@gmail.com" }, {"type" : "alias", "value" : "xyz@gmail.com" }, {"type" : "alias", "value" : "abc@gmail.com" }, {"type" : "alias", "value" : "cyd@gmail.com" }].

Can some one help me with the above seniors. Appreciate your help.

Thanks, 

[Anonymous]

Hi,

   Could you please advise these two requirements as columns from same input data set or should we consider these two requirements independently?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Bharath12]

Hi Nikhil,

both columns from same input data set.

Thanks,

Ram 

[Anonymous]

Please look at below url if it helps

https://community.talend.com/t5/Design-and-Development/From-tFileFetch-delimited-file-to-JSON/m-p/45...

[Anonymous]

@rchinta 

I tried something quick but unfortunately it didn't work out. We can do parsing through routine mode but I am travelling this week and do not have sufficient time to check it 😞

@uganesh  or @DataTeam  may be interested in this one.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[akumar2301]

Thanks Nikhil.
2nd one Looks like nested json array. I will check this.
I think need to reformat the input to prepare json.

[Anonymous]

@uganesh 

Yeah Abhi... I was thinking to do tnormalize and then process... I am in between travel and could not think much on this one.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[DataTeam1]

@rchinta  are you sure you want to get this?:

"email" : [ {"type" : "workemail", "value" : "12345@gmail.com" }, {"type" : "alias", "value" : "xyz@gmail.com" }, {"type" : "alias", "value" : "abc@gmail.com" }, {"type" : "alias", "value" : "cyd@gmail.com" }]

I can show you how to get this:

"email": [
{
"work_email": "12345@gmail.com",
"Alias": "[\"xyz@gmail.com\",\"abc@gmail.com\",\"cyd@gmail.com\"]"
}
]

Here is the solution:

First you have to normalize Alias column then use tWriteJSONField

then send it to tFileOutputJSON

[akumar2301]

my Idea is to first reformat the input

input example

12345@gmail.com;abc@gmail.com,def@gmail.com

12346@gmail.com;abc@gmail.com,def@gmail.com

formatted data

Then Parse it with tWriteJsonFields

My Job

Output

{"email":
[{"type":"workmail","value":"12345@gmail.com"},
{"type":"alias","value":"abc@gmail.com"},
{"type":"alias","value":"def@gmail.com"}]
}
{"email":[{"type":"workmail","value":"12346@gmail.com"},
{"type":"alias","value":"abc@gmail.com"},
{"type":"alias","value":"def@gmail.com"}
]}

similar approach can be done for Requirement 1. refer attached job.

jsonparsertest_0.1.zip

[Bharath12 · Accepted solution]

Thanks @uganesh . Could you please help me solution for the below example as well.

Requirement 1:

source from mysql DB:

id

123,456,7458,4566897,32132164

I need JSON output like below.

"ID":["123","456","7458","4566897","32132164"]

Thanks,