Parse JSON column

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!

 


Accepted Solutions
Nine Stars

Re: Parse JSON column

Hi ,

 

Please find the job below to split json field.

 

Json1.PNGJson2.PNG

 

Regards,

 

 

Veeru Boppudi

All Replies
Sixteen 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.
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 !!
Five Stars

Re: Parse JSON column

Hello,

I am trying to parse data from a JSON file I have, I am using tFileInputJson and logging the rows.

When the file has only one record, the job runs fine and give me the desired result.

But when the file has more than one record, I get the error "Unable to find next state", I am attaching all the files.

Could you please let me know how I can run this job for the file with multiple records.

 

Please help!

 

Thanks,

Alok