Convert rest API output json into Datatable

1

Hi

is there a way to convert the json response which contain a json array into a Datatable. i get the json result as output of an REST API .

Please help

2

Hi,

You can parse individual key and value of json using JSON activity and then add them in a datatable using Add Row activity provided under data table

Regards,
Amit Anand

3

Hi Amit
my json response contain a json array , is there any documentation on how to parse JSON Array using json activity . I am not getting a clue on how to loop and get values also how to pass the counter to increment it

4

Hi Amit

i used json activity getter method. Output mapping is shown below here DeletedSessions is a json array which contain a number of objects.

First assignment give JArray and while printing it shows entire JSON Array
second one give proper result also
third one is throwing error . I dont know how many objects are present in Array . so i am calling this activity in a while loop with condition count < APIOutputArray.Count

error is unable to parse json unexpected character while parsing path indexer:c

Please let me know how to loop through this array

image
image731×268 11.1 KB

5

Hi,

Json path has not been given correctly.

Can you post a sample json which you are trying to parse.

Regards,
Amit Anand

6

Hi Amit

PFB. I am trying to parse Deletesessions array

{
“meta”: {
“contexturl”: “”,
“REQUESTUUID”: “402c5f28-ab9c-11ea-bb37-0242ac130002”,
“GLOBALUUID”: “402c5f28-ab9c-11ea-bb37-0242ac130002”
},
“data”: {
“DeletedSessions”: [
{
“be_interface_id”: “1001”,
“be_session_id”: “O:2980347”,
“deleted”: “Y”,
“rcre_time”: “12-JUN-20”,
“remarks”: “NA”,
“session_id”: “6b8234d733805ff141770ba9d92d2a0d34198eeb”,
“user_Id”: “APERO8_AL1”
},
{
“be_interface_id”: “1002”,
“be_session_id”: “hazHx2gxTRTmtYhuZ+WWww==R”,
“deleted”: “Y”,
“rcre_time”: “12-JUN-20”,
“remarks”: “NA”,
“session_id”: “6b8234d733805ff141770ba9d92d2a0d34198eeb”,
“user_Id”: “APERO8_AL1”
}
]
}
}

7

Hi,

To iterate let’s say “Deleted Sessions”, you can follow this approach:

  1. Create a datatable in the process, with columns =json (deleted sessions keys)
    image
    image
  2. Now, you can use Advanced Loop with loop type=JSON and configure the following parameters:
    image
    here, json input=json file path
    json path=the path in the JSON file from where you want to iterate:
    image
    image733×100 5.13 KB

    and,Output=DataROwVIew object in which json values will be returned one by one
  3. Now, inside the Advanced loop, you can iterate the output JsonRow object and extract the text in an argument and add it to the datatable using the AddRow activity.
    image
  4. With this configuration, your datatable gets populated with the JSON data.
  5. Later on, you can even export the datatable or print reach row of the datatable to confirm.

Thanks,
Anshu

8

Thanks a lot for the help anshu . it worked :slight_smile: