Fetch the data from JSON

[Riya1 (Riya)]

Hi,
How to fetch the machine name and the state of it from this json output and store it in excel file.

@"
{
“”@odata.context"“: ““https://***********************””,
“”@odata.count”": 3,
““value””: [

{
““HostMachineName””: "“DESKTOP-7899008"”,
““MachineId””: 1438,
““MachineName””: "“DESKTOP-7899008"”,
““State””: ““Available””,
““ReportingTime””: ““2024-06-18T13:43:44.033Z””,
““Info””: null,
““IsUnresponsive””: false,
““LicenseErrorCode””: null,
““OrganizationUnitId””: 5446,
““FolderName””: ““yuihg””,
““RobotSessionType””: ““LongLived””,
““Version””: "“21.10.4"”,
““Source””: ““Assistant””,
““DebugModeExpirationDate””: null,
““InstallationId””: null,
““Platform””: ““Unknown””,
““Id””: 5910899

},

{
““HostMachineName””: ““DESKTOP-M889ik””,
““MachineId””: 1470,
““MachineName””: ““DESKTOP-M889ikj””,
““State””: ““Disconnected””,
““ReportingTime””: ““2024-05-06T14:52:09.94Z””,
““Info””: null,
““IsUnresponsive””: false,
““LicenseErrorCode””: null,
““OrganizationUnitId””: 54tyuh,
““FolderName””: ““uiojh””,
““RobotSessionType””: ““LongLived””,
““Version””: "“21.10.4"”,
““Source””: ““Assistant””,
““DebugModeExpirationDate””: null,
““InstallationId””: null,
““Platform””: ““Unknown””,
““Id””:

},

}"

[ashokkarale]

@Riya1,

Follow this solution.

Deserialize Json to DataTable with a Json Column

@ajgb find some starter help here: JSON-QueueItem_To_Datatable.xaml (10.6 KB) for a later parsing the specialcontent.string just Parse the Content of SpecificData into a JObject - jo with JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(jo(“DynamicProperties”).toString) the dictionary can be retrieved and deserialized

Thanks,
Ashok

[vrdabberu (Varunraj Dabberu)]

Hi @Riya1

=> Build Data Table
Output → dt

=> Use Read Text File activity to read the JSON text file store the output in a variable say jsonString.

=> Use Deserializ JSON activity and pass jsonString as Input and store the output in a variable say jsonObject.

=> Use FOr Each Loop and give the below condition:

For Each currentJToken in jsonObject("value")
   Add Data Row Activity
         -> Array Row: New Object() {currentJToken("MachineName").ToString(), currentJToken("State").ToString()}
         -> DataTable: dtOutput
End For Each

=> Use Write Range Workbook to write dtOutput back to excel.

XAML:
Sequence87.xaml (11.6 KB)

Regards

[postwick (Paul Ostwick)]

Deserialize JSON activity (UiPath.WebApi.Activities) into a JObject.

Then Deserialize JSON Array, passing yourJObjVar(“value”).ToString as the Json string.

Then For Each through yourJArrayVar and add data row using currentItem(“HostMachineName”).ToString to get the machine name value.

Then Write Range your datatable to Excel.

[Riya1 (Riya)]

Hi @postwick Can you please provide the sample xaml file?

[postwick (Paul Ostwick)]

I don’t have one. I gave you the exact steps. Work on it and if you run into a problem let us know.

[vrdabberu (Varunraj Dabberu)]

Hi @Riya1

You can check this flow:

Fetch the data from JSON

Hi @Riya1 => Build Data Table Output → dt => Use Read Text File activity to read the JSON text file store the output in a variable say jsonString. => Use Deserializ JSON activity and pass jsonString as Input and store the output in a variable say jsonObject. => Use FOr Each Loop and give the below condition: For Each currentJToken in jsonObject("value") Add Data Row Activity -> Array Row: New Object() {currentJToken("MachineName").ToString(), currentJToken("State").ToString()} …

Please download UiPath.WebAPI.Activities package

Regards

[Riya1 (Riya)]

Getting below error:

[postwick (Paul Ostwick)]

State isn’t your array. Value is your array. Change it to str_JsonOutput(“value”).ToString

And you don’t For Each over jsonString, you for each over the output of Deserialize JSON Array.

[Riya1 (Riya)]

HI @vrdabberu ,

I am getting error as Attached xaml file is not valid.

[Riya1 (Riya)]

Still getting same error.

[ppr (Peter Preuss)]

Quick dirty
Parse the JSON into a JObject

myJObject

the use assign activities and do

then we can split out the needed two columns by:

[vrdabberu (Varunraj Dabberu)]

Hi @Riya1

Check the below zip file
BlankProcess43.zip (143.9 KB)

Regards

[postwick (Paul Ostwick)]

It’s not Value it’s value. Case sensitive.

Main.xaml (12.8 KB)

[postwick (Paul Ostwick)]

It outputs a JArray, not a string.