Skip to main content
Announcements
A fresh, new look for the Data Integration & Quality forums and navigation! Read more about what's changed.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to extract JSON (multiline) child records?

I am having exactly the same problem as mentioned below in this topic. Can anyone suggest how to resolve the same?

 

https://www.talendforge.org/forum/viewtopic.php?pid=187996

Labels (2)
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi 

Read the file with 'xpath' model and set the Loop xpath query to "/lessons/files", you should be able to extract all the data, see

0683p000009Lueb.png

I have made a testing and it works, let me know if you have further questions. 

 

Regards

Shong

View solution in original post

9 Replies
Anonymous
Not applicable
Author

Hi
Can you provide an example file for testing? What are your expected result?

Regards
Shong
Anonymous
Not applicable
Author

Sure i Will send. Actually my input is a json file and output is an excel. May I know how can i attach a excel file.

 

Anonymous
Not applicable
Author

You are able to add attachments in your post using Reply button rather than Quick Reply.
Anonymous
Not applicable
Author

Here is the sample input json:

[{"_id":"58e3e47063fe11cb5aa36cea","title":"SAMHSA IOP Lessons 1 - 3","lessons":[{"title":"Lesson 1: Alcohol","surveyUrl":"","files":[{"size":485230,"mimeType":"application/pdf","mimetype":"application/pdf","_filename":"1_Alcohol_RP1.pdf","_public":true,"_id":"696a8cb4-45a7-438c-8648-20600995eb96","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:16:50.881Z","ect":"2017-04-04T21:16:50.881Z"},"_data":{}},{"size":47069296,"mimeType":"video/mp4","mimetype":"video/mp4","_filename":"Alcohol.mp4","_public":true,"_id":"de8819aa-6eea-4037-97e9-0687af3061b9","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:17:00.480Z","ect":"2017-04-04T21:17:00.480Z"},"_data":{}},{"size":5693373,"mimeType":"audio/mp3","mimetype":"audio/mp3","_filename":"Alcohol_1.mp3","_public":true,"_id":"cd49904f-3c07-47d1-a69a-105f982ca0ec","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:22:55.780Z","ect":"2017-04-04T21:22:55.780Z"},"_data":{}}],"textAvailable":true,"id":1491330153,"completion":0,"time":"","revisits":0,"comprehension":0,"videoAvailable":true,"audioAvailable":true},{"title":"Lesson 2: Boredom","files":[{"size":29475149,"mimeType":"video/mp4","mimetype":"video/mp4","_filename":"Boredom.mp4","_public":true,"_id":"49acb6df-e74d-4c86-b0a9-fbd7d08b20b5","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:26:56.570Z","ect":"2017-04-04T21:26:56.570Z"},"_data":{}},{"size":4245573,"mimeType":"audio/mp3","mimetype":"audio/mp3","_filename":"Boredom.mp3","_public":true,"_id":"5afc0339-81df-4d0a-ab15-ea0b25ee011c","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:28:44.714Z","ect":"2017-04-04T21:28:44.714Z"},"_data":{}},{"size":358300,"mimeType":"application/pdf","mimetype":"application/pdf","_filename":"1_Boredom_RP2.pdf","_public":true,"_id":"8e6dbeb5-ac50-4392-ac3a-31ac96f69564","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:29:07.291Z","ect":"2017-04-04T21:29:07.291Z"},"_data":{}}],"videoAvailable":true,"audioAvailable":true,"textAvailable":true,"id":1491341420,"completion":0,"time":"","revisits":0,"comprehension":0},{"title":"Lesson 3: Avoiding Relapse Drift","surveyUrl":"","files":[{"size":687737,"mimeType":"application/pdf","mimetype":"application/pdf","_filename":"1_Avoiding Relapse Drift_RP3A.pdf","_public":true,"_id":"7b27d3f7-3166-42fc-88b6-76cd9c366b0a","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:30:12.156Z","ect":"2017-04-04T21:30:12.156Z"},"_data":{}},{"size":26082267,"mimeType":"video/mp4","mimetype":"video/mp4","_filename":"Avoiding Relapse Drift v1.mp4","_public":true,"_id":"f0326221-a834-44c9-b78b-94061a7de2b5","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:31:00.683Z","ect":"2017-04-04T21:31:00.683Z"},"_data":{}},{"size":3141799,"mimeType":"audio/mp3","mimetype":"audio/mp3","_filename":"Avoiding Relapse Drift.mp3","_public":true,"_id":"e3383266-a6d3-416f-8473-31931ce4ea1a","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-04T21:32:06.361Z","ect":"2017-04-04T21:32:06.361Z"},"_data":{}}],"textAvailable":true,"videoAvailable":true,"audioAvailable":true,"id":1491341677,"completion":0,"time":"","revisits":0,"comprehension":0},{"title":"Lesson 1-3 Worksheet","surveyUrl":"https://www.surveygizmo.com/s3/3479631/Crossroads-IOP-Lesson-1-3-copy","files":[],"id":1491414764,"completion":0,"time":"","revisits":0,"comprehension":0}],"programType":"CORE East IOP","_acl":{"creator":"58ddb162db2ef12435d8cb85"},"_kmd":{"lmt":"2017-04-05T18:01:06.662Z","ect":"2017-04-04T18:22:40.275Z"},"count":0}]

Output : 

Download the expected file from here this link:

https://drive.google.com/file/d/0B_9Cef2srTxudkpsVklpTVBXbDdOanFCOG1zLXVJdWFrdW5v/view?usp=sharing

 

Anonymous
Not applicable
Author

Hi Shong,
Any thoughts / updates on this problem i was requesting your help? It would
be appropriate if you could show/sent me as a job itself as i tried many
ways and yet struck.
Anonymous
Not applicable
Author

Hi 

Read the file with 'xpath' model and set the Loop xpath query to "/lessons/files", you should be able to extract all the data, see

0683p000009Lueb.png

I have made a testing and it works, let me know if you have further questions. 

 

Regards

Shong

Anonymous
Not applicable
Author

Can you please send me the job as an attachment which will be useful for me
to proceed.
Anonymous
Not applicable
Author

see attachment, I export the job items from version 6.4.0. You should use the same version or higher versions to import the job.
forum27427.zip
Anonymous
Not applicable
Author

You are the man...!!!! This does works. Thanks for your suggestion.