Help with converting JSON response to table

[welshsteve]

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

[Anonymous · Accepted solution]

@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​