Convert HTTP Response with nested JSON to DataTable

Help Activities
, ,
1

Following is the format of my HTTP Request JSON output.I want to convert into data table with column PROFITCENTERCD,PERIODID2_TSTAMP and BABUCGIDGRS.Need to neglect metadata from ‘results’ array and the remaining fields should be converted as a datacolumn. Thanks in advance.

{
“d”: {
“__count”: “2”,
“results”: [
{
“__metadata”: {
“id”: “https://678989T/xyz(0l)”,
“uri”: “https://678989T/xyz(0l)”,
“type”: “calcview.SOPUTY”
},
“PROFITCENTERCD”: “2139”,
“PERIODID2_TSTAMP”: “/Date(1664668800000)/”,
“BABUCGIDGRS”: “0.000000”
},
{
“__metadata”: {
“id”: “https://678989T/xyz(0l)”,
“uri”: “https://678989T/xyz(0l)”,
“type”: “calcview.SOPUTY”
},
“PROFITCENTERCD”: “2199”,
“PERIODID2_TSTAMP”: “/Date(1664698800000)/”,
“BABUCGIDGRS”: “0.000000”
}
]
}
}

2

Hi @Raj578

Please take a look at this solution:

Step 1: Use the Deserialize JSON activity to parse the JSON response and convert it into a JObject.

Step 2: Use the Select Token activity to extract the ‘results’ array from the JObject.

Step 3: Iterate through the items in the ‘results’ array and create a DataRow for each item with the desired fields.

Step 4: Use the For Each activity to iterate through the items in the resultsArray. Set the TypeArgument to Newtonsoft.Json.Linq.JToken.

Step 5: Inside the loop, create a DataRow for each item and extract the desired fields:

row("PROFITCENTERCD") = item.SelectToken("PROFITCENTERCD").ToString()
row("PERIODID2_TSTAMP") = item.SelectToken("PERIODID2_TSTAMP").ToString()
row("BABUCGIDGRS") = item.SelectToken("BABUCGIDGRS").ToString()

After the loop, you will have a data table containing the desired columns and data.

Hope this helps,
Best Regards.

1 Like
3

Thanks for the quick reply, followed your steps and i am able to get the desired output.As a next step, is it possible to dynamically create the Datatable using the fields in the JSON Array.For Ex…In the above given JSON object i need to create datatable with columns PROFITCENTERCD,PERIODID2_TSTAMP,BABUCGIDGRS but it is not static the fields might change based on the endpoint.

4

For some dynamics we can have a look at this longer oneliner:

grafik
grafik961×126 32.1 KB

And can break the idea down to smaller parts and/or decompose it to essential activities

myRowJOs | List(Of JObjects) =

myJObject("d")("results").Cast(of JObject).Select(Function (x) new JObject(x.Properties.Where(Function (p) Not p.Name.Equals("__metadata"))))

we constructed new JObjcets where we removed the __metadata property
(could be extended by a block list approach as well)

then we convert it to a DataTable:

dtResult =

Jarray.FromObject(myRowJOs).ToObject(Of DataTable)
2 Likes
5

This works as well…Thanks

6

Is it possible to format “PERIODID2_TSTAMP”: “/Date(1664668800000)/” to
“PERIODID2_TSTAMP”: “1664668800000)" before assigning to the datatable.
Because when this “PERIODID2_TSTAMP”: “/Date(1664668800000)/” is assigned to datatable it is automatically converting to date format which i dont want.Thanks in advance

7

We recommend scoping 1 Topic = 1 case, as other researchers can easily find solutions for their similar cases.

For the second topic:

In such case, we can customize the Parsing done with statement JObject.Parse(…)
by setting Parsing options and do not parsing date.

Also, have a look here:

grafik
grafik676×544 71.4 KB

8

Apologize for adding another topic here, will take care next time.

Where i have to incorporate your logic, my thought is before assigning the data to data table can we convert into unixnbr and assign? or we have to loop the datatable to do the conversion once it is created? pls let me know the code solution for the first approach if possible.Thanks

9

You can still open a new topic

We can check the resulting Datatype:

grafik
grafik946×82 24.8 KB

So one of many options could be

  • Convert into DataTable as described above:
  • Add Data Column Activity: PERIODID2_SEC, Datatype: Your Choice: String, Double…
  • Convert - e.g for each row loop
  • Remove origin Column
  • Rename and reorder the remaining Columns
10

i am not sure why we have to identify the datatype.
Datatable is created and then can we use something like below to convert the PERIODID2_TSTAMP
column value to unixtime

DT.asenumerable.Tolist.Foreach(function(x) x(“PERIODID2_TSTAMP”)=x(“PERIODID2_TSTAMP”).ToUnitTime).Copytodatatable

I am not sure about the linq so need help there

11

@Raj578
Please note the recommendation to open a new topic as more and more 2 requests are mixing up

We demonstrated, that the Datatype is DateTime and the requested conversion will result in a different datatype

RnD can be done within the immediate panel
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

LINQ Training Start here:
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

Update Options here:

If further assistance is needed for the conversion part, just open a new topic on this specific case. Thanks

1 Like
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.