Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi. I'm not very knowledgeable on JSON, and am trying to interpret a JSON response from an API call and convert it into a table using Talend.
This is the tRESTClient response:
{
"choice_category": "type",
"choice_category_dependence": {
"Admin": [
"Email",
"Post",
"Filing"
],
"Services": [
"Customer Call",
"Returns",
"Customer Email"
],
"Other": [
""
],
"Training": [
"Online Course",
"Classroom Course"
]
}
}
I need to extract this into a table format for a database so I can use it as a lookup table for some data transformation work I'm working on.
I've tried using the tExtractJSONFields component, but unsure how to structure my schema etc.
To explain how I want this to work, if I were to put this into a CSV, this how I'd want the CSV to look like.
"choice_category","choice_category_type","choice_category_subtype"
"type","Admin","Email"
"type","Admin","Post"
"type","Admin","Filing"
"type","Services","Customer Call"
"type","Services","Customer Email"
"type","Services","Returns"
"type","Other",""
"type","Training","Online Course"
"type","Training","Classroom Course"
Is anybody able to assist or give me any pointers at all? I'm a complete novice with this sort of stuff
@Steve Williams there are multiple array in the Json string, you can't extract all array values with one tExtractJsonField, I think you cache the string in memory and in use different tExtractJsonField components to extract each part in next subjobs, and append the each part to the same file, the job looks like:
tRestClient--main--tHashOutput1
|onsubjobok
tHashinput1--main-tExtractJason2-->-tJavaRow1
|onsubjobok
tHashInput2--main--tExtractJason2-->tMap1-->tFileOutputDelmited1
|onsubjobok
extract data from Services array and append data to the same file
|onsubjobok
extract data from Other array and append data to the same file
|onsubjobok
extract data from Training array and append data to the same file
tHashOutput: cache the json string in memory.
tHashInput1: read json string from memory,
tExtractJsonField1: extract choice_category value only
tJavaRow1: store choice_category value to global variable for used later, eg:
globalMap.put("key",input_row.choice_category)
tHashInput2: read json string from memory,
tExtractJsonField2: extract choice_category_subtype value from Admin array.
tMap1: in the output table, add two new column, one is choice_category, set it's expression as:
(String)globalMap.get("key")
another column is choice_category_type, set it's expression as "Admin"
other arrays follows the same logic.
Hope it helps.
Regards
Shong
@Steve Williams there are multiple array in the Json string, you can't extract all array values with one tExtractJsonField, I think you cache the string in memory and in use different tExtractJsonField components to extract each part in next subjobs, and append the each part to the same file, the job looks like:
tRestClient--main--tHashOutput1
|onsubjobok
tHashinput1--main-tExtractJason2-->-tJavaRow1
|onsubjobok
tHashInput2--main--tExtractJason2-->tMap1-->tFileOutputDelmited1
|onsubjobok
extract data from Services array and append data to the same file
|onsubjobok
extract data from Other array and append data to the same file
|onsubjobok
extract data from Training array and append data to the same file
tHashOutput: cache the json string in memory.
tHashInput1: read json string from memory,
tExtractJsonField1: extract choice_category value only
tJavaRow1: store choice_category value to global variable for used later, eg:
globalMap.put("key",input_row.choice_category)
tHashInput2: read json string from memory,
tExtractJsonField2: extract choice_category_subtype value from Admin array.
tMap1: in the output table, add two new column, one is choice_category, set it's expression as:
(String)globalMap.get("key")
another column is choice_category_type, set it's expression as "Admin"
other arrays follows the same logic.
Hope it helps.
Regards
Shong