problem while reading json file input

Five Stars

problem while reading json file input

Hi, am having a json input file with the following format. If I read it using tfileinputjson, I may have n number of columns since each id number is an element in json structure. 

"result_ok": true,
"total_count": 79463,
"page": "7",
"total_pages": 15893,
"results_per_page": "5",
"data": [
"id": "55",
"contact_id": "",
"status": "Partial",
"is_test_data": "0",
"date_submitted": "2018-09-13 02:00:59 GMT",
"session_id": "1536804052_5b99c4d43e0069.81751855",
"language": "English",
"date_started": "2018-09-13 02:00:59 GMT",
"link_id": "191418",
"url_variables": {
"region": {
"key": "region",
"value": "emea",
"type": "url"
"uni": {
"key": "uni",
"value": "qsfair",
"type": "url"
"country": {
"key": "country",
"value": "uk",
"type": "url"
"ip_address": null,
"referer": "",
"user_agent": null,
"response_time": 0,
"data_quality": [],
"longitude": "",
"latitude": "",
"country": "",
"city": "",
"region": "",
"postal": "",
"dma": "",
"survey_data": {
"4": {
"id": 4,
"type": "MENU",
"question": "<strong>What national passport will you be travelling on when you study overseas?</strong>",
"section_id": 13,
"shown": false
"5": {
"id": 5,
"type": "RADIO",
"question": "<strong>What gender are you?</strong>",
"section_id": 13,
"shown": false
"10": {
"id": 10,
"type": "RADIO",
"parent": 9,
"question": "Full-time study",
"section_id": 20,
"shown": false
"11": {
"id": 11,
"type": "RADIO",
"parent": 9,
"question": "Part-time study",
"section_id": 20,
"shown": false
"15": {
"id": 15,
"type": "RADIO",
"question": "<strong>At what level are you currently planning to study?</strong>",
"section_id": 20,
"shown": false
} and so on......

I want the columns id, type, question, section_id, shown along with the data. But while I try reading the input using tfileinputjson am facing this issue as shown in the picture. I have tried to read the element subquery as a single row and then extract fields using textractjsonfields. But it didn't work out. How can I read the required data from each ids? Pls help

Forteen Stars

Re: problem while reading json file input



you can not extract all data with single textractjsonfields component

it is because you are required data from "named" JSON keys, not from the array where you could use [*] but from the dynamic list of keys - 4,5,10,11 etc


you need do this with some java code, check for examples:


or search on StackOverflow for - "extract JSON keys name" for the more appropriate solution 


regards, Vlad


What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables


How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration


Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.