Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Accepted Solutions
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]
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]
