Skip to main content
Solved

I have to write below Multi array list in to a file through JSON call.How can I perform this?

  • July 23, 2023
  • 1 reply
  • 260 views
M
Data Pioneer
Forum|alt.badge.img+1

Json : 

[
   {
      "Headers":[
         "Empid",
         "Name",
         "dept",
         "EMAIL"
      ],
      "ID":"Table_112",
      "Rows":[
         [
            "100",
            "John",
            "Finance",
            "John@gmail.com"
         ],
         [
            "101",
            "Smith",
            "HR",
            "Smith@gmail.com"
         ],
         [
            "102",
            "Derek",
            "Marketing",
            "Derek@gmail.com"
         ]
      ]
   }
]

 

Expecting the output in below format:

Empid Name dept EMAIL ID
100 John Finance John@gmail.com Table_112
101 Smith HR Smith@gmail.com  
102 Derek Marketing Derek@gmail.com  

 

 

 

I have created a plan with below details:

After running a plan with JSON CALL step below response file got created.

 

 

 

Based on response file i have given below columns in Data Streams 

 

 

 

I am not getting the out put what i expected. Appreciate your help and support .

 

Best answer by Lisa Kovalskaia

Hi @mahesh Ar, I think to parse this kind of JSON you'll have to define embedded data streams, like so:

 

With Rows and Headers as data streams, each individual value/datapoint will be parsed out into a separate row. 

 

From there you can use the Record Descriptor Builder on the Rows stream to identify groups of values that should be treated attributes of the same record. Based on the position of the value in its group you can also identify the type of attribute each value represents (empid/name/etc.). I would suggest using the Representative Creator step to designate attribute columns and populate them with values. Attached is the sample plan to get you started - hope this helps!

1 reply

Lisa Kovalskaia
Ataccamer
Forum|alt.badge.img+3
  • Lisa KovalskaiaAtaccamer
  • Ataccamer
  • 73 replies
  • Answer
  • July 31, 2023

Hi @mahesh Ar, I think to parse this kind of JSON you'll have to define embedded data streams, like so:

 

With Rows and Headers as data streams, each individual value/datapoint will be parsed out into a separate row. 

 

From there you can use the Record Descriptor Builder on the Rows stream to identify groups of values that should be treated attributes of the same record. Based on the position of the value in its group you can also identify the type of attribute each value represents (empid/name/etc.). I would suggest using the Representative Creator step to designate attribute columns and populate them with values. Attached is the sample plan to get you started - hope this helps!


Community Terms of Use & ConditionsCookie settingsAccessibility statement