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": "http://www.surveygizmo.eu/s3/90100193/2f67b9b67374",
"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

Highlighted
Forteen Stars

Re: problem while reading json file input

Hi,

 

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:

https://community.talend.com/t5/Design-and-Development/Unable-to-parse-JSON-quot-name-quot-using-tEx...

 

https://community.talend.com/t5/Design-and-Development/Need-help-using-tExtractJSONFields-to-parse-J...

 

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

 

regards, Vlad

-----------

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch 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