Five Stars

Parse JSON column

Hi,

I have a text file with 2 columns:

  • First column contains Keywords. e.g. "Apple TV"
  • Second column contains one JSON with all the returned items for that keyword.

The delimiter between the two columns are 2 pipe delimiters "||"

Example of the file:

apple tv||{"items":{"items": {"item":[{"itemId": 35364564576},{"itemId": 23425436456}]}}}
amazon echo||{"items":{"items": {"item":[{"itemId": 4563457657},{"itemId": 67868678678}]}}}

I need to build a job that extract the keyword and extract it's relevant items from the JSON

How I would like the output to look like:

KeywordItemID
apple tv35364564576
apple tv23425436456
amazon echo4563457657
amazon echo67868678678


I know it might be very simple, but It's been a while since I used Talend and from some reason can't get it work.

Thanks in advance for your help!

 

Tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars

Re: Parse JSON column

Hi ,

 

Please find the job below to split json field.

 

Json1.PNGJson2.PNG

 

Regards,

 

 

Veeru Boppudi
3 REPLIES
Fifteen Stars

Re: Parse JSON column

First you need to split the columns of your file. I'm assuming you can do that using the tFileInputDelimited component. You should have 2 columns.

Next you need a tExtractJsonField component. There are 2 things to think about here; your keywords column needs to be a "pass-through" column (by that I mean no JSON Path should be specified), the items data will need a JSON path. BUT you will also need a Loop set.

Take a look at this question and see if you can extrapolate from it (https://community.talend.com/t5/Design-and-Development/JSON-Array-values-to-Rows-is-there-a-better-w...).

Unfortunately I'm at an airport with just a smart phone so cannot really go into much more detail. Hope this helps.
Rilhia Solutions
Nine Stars

Re: Parse JSON column

Hi ,

 

Please find the job below to split json field.

 

Json1.PNGJson2.PNG

 

Regards,

 

 

Veeru Boppudi
Five Stars

Re: Parse JSON column

Super! thankssss !!