Storing the values in JSON output to Data table

[Harsha_Vemula]

Hi,

I want to store the values coming as the output in JSON format. Below is my JSON. Can some one help me how I can store the values of Status into data table.
Expectation: Column name should be Status and the values in it should be New, New, Delete,…etc, So that I can filter it and get the count of “New” status.

“value”: [
{
“Status”: “New”
},
{
“Status”: “New”
},
{
“Status”: “New”
},
{
“Status”: “New”
},
{
“Status”: “New”
},
{
“Status”: “Abandoned”
},
{
“Status”: “Deleted”
},
{
“Status”: “Deleted”
},
{
“Status”: “Deleted”
},
{
“Status”: “Deleted”
},
{
“Status”: “Deleted”
},
{
“Status”: “Abandoned”
},
{
“Status”: “Deleted”
},
{
“Status”: “Deleted”
},
{
“Status”: “Deleted”
},
{
“Status”: “Deleted”
},
{
“Status”: “Deleted”
},
{
“Status”: “Abandoned”
}
]

[saurabhB]

read the json and use Deserealise JSON activity (install WebAPI module from manage packages) and set output to say variable “testJson”
Following code will give you the count of “New” items in the complete json:
testJson.SelectToken(“value”).Where(Function(x) x.SelectToken(“Status”).ToString.Equals(“New”)).Count

No need to change to datatable , you can do that too , I will add new comment on hot to convert json to datatable
Hope this helps

[jeevith (Jeevith Hegde, Ph.D.)]

Hi @Harsha_Vemula,

There are plenty of ways to achieve this. I agree with @saurabhB that you do not need to convert to a datatable to get count of certain values.

In both approaches you will need the following dependency (to ensure your input string is converted to Json object)
or the namespace in imports

1. The datatable approach
   

   
   
   JsonCountValueUnique.xaml (9.6 KB)

2. The query approach
   

   

@saurabhB you can trim down the expression even more as the InputJson is already a json object and you can directly access the first key in this case “value” which is a Jarray without using .SelectToken

In the end this expression should get the same answer

InputJson(“value”).Where(Function(Jobject) Jobject(“Status”).ToString.Equals(“New”)).Count

What is this expression doing?
Your Json Object is InputJson, which contains a key called “value”. Then since this is a json array we can use the Where method to iterate through all possible elements in the array and each element in your json array is again a json object (with a key and value (“Status”:“Value”) so we can then use a condition to check for elements with value equal to “New”/“Deleted”/“Abandoned”. This will result in System.Linq.Enumerable which can be finally queried for count of elements with it.

If you are used to List Comprehensions / Lamda functions in Python this syntax might be easier to understand.

JsonCountValueUniqueShorterMethod.xaml (6.8 KB)

Your Json string :
JsonValueCounts.json (870 Bytes)

A tip for next time is to use the code editor option when you paste your Json string, else the double quotes are not the same and it takes much longer for forum members to resolve your query.

[saurabhB]

json parse.xaml (8.0 KB)
This code will help you to convert json to datatable , once you the logic you can create the table of required format

[Harsha_Vemula]

Thanks for your support.

[Harsha_Vemula]

Thank you so much.