problem while reading json file input

Anonymous — Wed, 03 Apr 2019 14:26:57 GMT

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": "What national passport will you be travelling on when you study overseas?",
"section_id": 13,
"shown": false
},
"5": {
"id": 5,
"type": "RADIO",
"question": "What gender are you?",
"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": "At what level are you currently planning to study?",
"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

Re: problem while reading json file input

vapukov — Fri, 05 Apr 2019 00:33:36 GMT

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-tExtractJSON/m-p/132301?advanced=false&author_id=45&collapse_discussion=true&filter=authorId&q=json&search_type=thread

https://community.talend.com/t5/Design-and-Development/Need-help-using-tExtractJSONFields-to-parse-JSON-input/m-p/24855?author_id=45&collapse_discussion=true&filter=authorId&page=2&q=json&search_type=thread

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

regards, Vlad

topic Re: problem while reading json file input in Talend Studio

problem while reading json file input

Re: problem while reading json file input