topic Re: how to convert delimited values to json array and key value pair in Talend Studio

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

Bharath12 — Sat, 16 Nov 2024 06:19:13 GMT

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:

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,

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

Anonymous — Fri, 15 Mar 2019 14:28:36 GMT

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 🙂

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

Bharath12 — Fri, 15 Mar 2019 15:11:20 GMT

Hi Nikhil,

both columns from same input data set.

Thanks,

Ram

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

Anonymous — Fri, 15 Mar 2019 15:17:01 GMT

Please look at below url if it helps

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

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

Anonymous — Sat, 16 Mar 2019 09:40:23 GMT

@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 🙂

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

akumar2301 — Sat, 16 Mar 2019 09:56:18 GMT

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

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

Anonymous — Sat, 16 Mar 2019 09:58:32 GMT

@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 🙂

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

DataTeam1 — Sat, 16 Mar 2019 12:01:19 GMT

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

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

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

akumar2301 — Sat, 16 Mar 2019 18:38:58 GMT

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

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

Bharath12 — Mon, 18 Mar 2019 02:02:54 GMT

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

Requirement 1:

source from mysql DB:

123,456,7458,4566897,32132164

I need JSON output like below.

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

Thanks,

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

Bharath12 — Mon, 18 Mar 2019 02:04:11 GMT

Thanks @uganesh . Can you please help me for the below example.

Requirement 1:

source from mysql DB:

123,456,7458,4566897,32132164

I need JSON output like below.

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

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

akumar2301 — Mon, 18 Mar 2019 07:20:43 GMT

Hello ,

In attached job , you have solution for both. Approach is similar so did
not explain it.

Also by error you have selected your comment as accepted solution. Marking
correct comment would help others.