topic Parse JSON column in Talend Studio

Parse JSON column

Anonymous — Fri, 09 Mar 2018 02:31:05 GMT

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:

Keyword	ItemID
apple tv	35364564576
apple tv	23425436456
amazon echo	4563457657
amazon echo	67868678678

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!

Re: Parse JSON column

Anonymous — Mon, 12 Mar 2018 04:20:01 GMT

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-way/m-p/52347#M17361).

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

Re: Parse JSON column

vboppudi — Mon, 12 Mar 2018 21:46:25 GMT

Hi ,

Please find the job below to split json field.

Regards,

Re: Parse JSON column

Anonymous — Tue, 13 Mar 2018 21:52:31 GMT

Super! thankssss !!

Re: Parse JSON column

Anonymous — Fri, 07 Sep 2018 02:03:28 GMT

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

Error.JPG
Job.JPG
Success.JPG
File_with_multiple_record.txt
File_with_Single_record.txt