How to extract JSON (multiline) child records?

Six Stars

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

Tags (1)

Accepted Solutions
Highlighted
Community Manager

Re: How to extract JSON (multiline) child records?

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

1.png

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

 

Regards

Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

Re: How to extract JSON (multiline) child records?

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

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: How to extract JSON (multiline) child records?

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.

 

Community Manager

Re: How to extract JSON (multiline) child records?

You are able to add attachments in your post using Reply button rather than Quick Reply.
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: How to extract JSON (multiline) child records?

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

 

Six Stars

Re: How to extract JSON (multiline) child records?

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.
Highlighted
Community Manager

Re: How to extract JSON (multiline) child records?

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

1.png

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

 

Regards

Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: How to extract JSON (multiline) child records?

Can you please send me the job as an attachment which will be useful for me
to proceed.
Community Manager

Re: How to extract JSON (multiline) child records?

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.
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: How to extract JSON (multiline) child records?

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

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch