Deserialize Json to DataTable with a Json Column

[ajgb]

Hi everyone,

Im connecting to Orchestrator Cloud API in order to get the information needed for the reportings of my processes. When I make a query to QueueItems, I get the data in Json format.

[
{
“QueueDefinitionId”: 132100,
“OutputData”: null,
“AnalyticsData”: null,
“Status”: “New”,
“ReviewStatus”: “None”,
“ReviewerUserId”: null,
“Key”: “4538d8fa-3cdd-4c58-83e5-dd20551a1deb”,
“Reference”: “10-53-41”,
“ProcessingExceptionType”: null,
“DueDate”: null,
“RiskSlaDate”: null,
“Priority”: “Normal”,
“DeferDate”: null,
“StartProcessing”: null,
“EndProcessing”: null,
“SecondsInPreviousAttempts”: 0,
“AncestorId”: null,
“RetryNumber”: 0,
“SpecificData”: “{"DynamicProperties":{"Dni":"12345","Nombre":"pepito"}}”,
“CreationTime”: “2020-07-10T08:53:41.92Z”,
“Progress”: null,
“RowVersion”: “AAAAAAJldrs=”,
“Id”: 24102848,
“ProcessingException”: null,
“SpecificContent”: {
“Dni”: “12345”,
“Nombre”: “pepito”
},
“Output”: null,
“Analytics”: null
},
{
“QueueDefinitionId”: 132100,
“OutputData”: null,
“AnalyticsData”: null,
“Status”: “Abandoned”,
“ReviewStatus”: “None”,
“ReviewerUserId”: null,
“Key”: “0bc50225-b78d-41a1-aecd-8b4e488dab31”,
“Reference”: null,
“ProcessingExceptionType”: null,
“DueDate”: null,
“RiskSlaDate”: null,
“Priority”: “High”,
“DeferDate”: null,
“StartProcessing”: “2020-07-10T08:50:57.893Z”,
“EndProcessing”: “2020-07-11T09:00:00.73Z”,
“SecondsInPreviousAttempts”: 0,
“AncestorId”: null,
“RetryNumber”: 0,
“SpecificData”: “{"DynamicProperties":{"Argument1":"Hola"}}”,
“CreationTime”: “2020-07-10T08:50:57.893Z”,
“Progress”: null,
“RowVersion”: “AAAAAAJscRc=”,
“Id”: 24101009,
“ProcessingException”: null,
“SpecificContent”: {
“Argument1”: “Hola”
},
“Output”: null,
“Analytics”: null
}
]

It is not a problem, because I use “Deserialize Json” activity to convert to DataTable:

The problem appear in the property “SpecificContent”, because there content is in Json format:

“Id”: 24101009,
“ProcessingException”: null,
“SpecificContent”: {
“Argument1”: “Hola”
},
“Output”: null,
“Analytics”: null

The error UiPath throw is:

Can anyone help me please? I would like to keep that Json format inside the DataTable.

Thank you!

[Pratik_Wavhal]

Hi @ajgb

So you are trying to extract the data from JSONObj Right ??

Below i have attached the sample workflow which can help you to solve your issue
JsonValueExtraction.zip (30.8 KB)

Mark as solution and like it if this helps you

Happy Automation

Best Regards
Er Pratik Wavhal

[ajgb]

The problem is that Im trying to deserialize a Json into a DataTable, but one of the properties is have a Json format (“SpecificContent”). UiPath throw an error because of this. I would like to keep that string (in Json format) as the content of that Column.

You can trying with this .txt

prueba.txt (1.8 KB)

[ppr (Peter Preuss)]

@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

[ajgb]

Thank you so much! With this solution works properly!