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

View solution in original post


All Replies
Highlighted
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
Highlighted
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.

 

Highlighted
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
Highlighted
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

 

Highlighted
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

View solution in original post

Highlighted
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.
Highlighted
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
Highlighted
Six Stars

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

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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog