topic Re: Parsing JSON list in Talend Studio

Parsing JSON list

stagliaferri1640766615 — Fri, 15 Nov 2024 22:01:14 GMT

Hello,

I need your help as usual.

I am parsing this JSON:

{

"id": 333444,

"applications": [

{

"id": 123456,

"attachments": [

{

"filename": "Cover Letter.docx",

"url": "",

"type": "cover_letter",

"created_at": "2023-03-07T10:07:01.491Z"

{

"filename": "CV.pdf",

"url": "",

"type": "resume",

"created_at": "2023-03-07T10:07:01.302Z"

}

using T ExtractJSONFields component as you can see in this picture :

them I am using a Tmap to map data in a sql table (using TDBOutput)

it works, but all values are stored in the same column : for example the value in the cover column is ["cover_letter","resume"]

how I achieve to get n lines, depending on the nbr of values?

(in this case I would like to have 2 lines, one for cover_letter, one for resume

thanks a lot!

Re: Parsing JSON list

rhall1 — Thu, 09 Mar 2023 00:26:05 GMT

Hi @sergio tagliaferri,

First of all, the JSON you have provided is slightly wrong. But I will put that down to a copying error. I have adjusted it to this....

{

"id": 333444,

"applications": [

{

"id": 123456,

"attachments": [

{

"filename": "Cover Letter.docx",

"url": "",

"type": "cover_letter",

"created_at": "2023-03-07T10:07:01.491Z"

{

"filename": "CV.pdf",

"url": "",

"type": "resume",

"created_at": "2023-03-07T10:07:01.302Z"

}

]

}

]

}

There was an extra comma and no closing square bracket for the outer loop. I have added these to test this.

This is an example of the job that I created to do this....

I've used 2 tExtractJSONFields. The first to deal with the outer loop, the second to deal with the inner loop. The first is configured like this....

See I am looping on...

$.applications[*]

I am then extracting the "id" and then the "attachments" inner loop. These values are passed to the next tExtractJSONFields component, shown here....

Notice the loop jsonpath query. I am simply using...

$[*]

...because only the looping section is passed through.

I then extract all of the fields from within the loop. Notice the "application_id" Json query is empty. This is because the value is just passed through from the first tExtractJSONFields component.

The result looks like this....

Starting job TestJSON123 at 00:18 09/03/2023.

[statistics] connecting to socket on port 3354

[statistics] connected

123456|Cover Letter.docx||cover_letter

123456|CV.pdf||resume

[statistics] disconnected

Job TestJSON123 ended at 00:18 09/03/2023. [Exit code = 0]

Re: Parsing JSON list

stagliaferri1640766615 — Thu, 09 Mar 2023 02:58:53 GMT

Hello @Richard Hall

sorry the JSON format I provided.

Thank you very much for helping me out, your solution works.

And the explanation is very clear.

I really appreciated it.

thank you

sergio

Re: Parsing JSON list

JSpence1684138720 — Fri, 15 Sep 2023 10:27:23 GMT

Thank you for the example, it helped a lot. I appreciate you. I also would like to help you. If you are searching for thesis writing services online then you can visit this https://academized.com/thesis-writing-service link where you can easily find professional essay writers who will help you with all your essay assignments. I am also using their professional essay writers to write my essay assignments because they give me my essay assignments on time.