Json Array to DataTable Error

Help Studio
1

Hey community,

I have trying to convert Json into DataTable but what is strange is that, I’m using Orchestrator HTTP Request, when I use “odata/Folders”, it works perfectly, but if I use “odata/QueueItems”, then I get an error saying “Assign: Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path ‘’, line 1, position 1.”.

Here is my code:

Code
Code573×552 14.9 KB

Error :

Error

I really appreciate any help!
Thanks

2

Hi @Tarek_Kariish ,

Did you try to see what is the response output, Can you share that with us?
Replace Private info with dummy data.

That might help us in giving you a solution.

Thanks,
Aditya

3

@Tarek_Kariish

Folders have only one level so it can convert but queueitems endpoint will give different levels of keys in json so we have to use a for loop and write the data using add data row activity

Cheers

4

[
{
“QueueDefinitionId”: ,
“Encrypted”: false,
“OutputData”: null,
“AnalyticsData”: null,
“Status”: “New”,
“ReviewStatus”: “None”,
“ReviewerUserId”: null,
“Key”: "“,
“Reference”: “",
“ProcessingExceptionType”: null,
“DueDate”: null,
“RiskSlaDate”: null,
“Priority”: “Normal”,
“DeferDate”: null,
“StartProcessing”: null,
“EndProcessing”: null,
“SecondsInPreviousAttempts”: 0,
“AncestorId”: null,
“RetryNumber”: 0,
“SpecificData”: "”,
“CreationTime”: “2023-05-10T07:56:54.123Z”,
“Progress”: null,
“RowVersion”: ““,
“OrganizationUnitId”: ,
“OrganizationUnitFullyQualifiedName”: "@gmail.com”,
“Id”: ,
“ProcessingException”: null,
“SpecificContent”: {
“Queue Name”: "",
“Queue Folder”: "”,
“Process Name”: “"
},
“Output”: null,
“Analytics”: null
},
{
“QueueDefinitionId”: ,
“Encrypted”: false,
“OutputData”: null,
“AnalyticsData”: null,
“Status”: “Successful”,
“ReviewStatus”: “None”,
“ReviewerUserId”: null,
“Key”: "",
“Reference”: "”,
“ProcessingExceptionType”: null,
“DueDate”: null,
“RiskSlaDate”: null,
“Priority”: “Normal”,
“DeferDate”: null,
“StartProcessing”: null,
“EndProcessing”: null,
“SecondsInPreviousAttempts”: 0,
“AncestorId”: null,
“RetryNumber”: 0,
“SpecificData”: “",
“CreationTime”: “2023-05-10T07:56:54.123Z”,
“Progress”: null,
“RowVersion”: "”,
“OrganizationUnitId”: ,
“OrganizationUnitFullyQualifiedName”: "@gmail.com”,
“Id”: ,
“ProcessingException”: null,
“SpecificContent”: {
“Queue Name”: "",
“Queue Folder”: “",
“Process Name”: "”
},
“Output”: null,
“Analytics”: null
}
]

5

And can you help me please, with extracting only the the key and value of :

For example :
Statue = Value
CreationTime = Value
Start Processing = Value

I want to write them into a sheet in excel.

Thank you very much

6

@Tarek_Kariish

Build a datatable with three coloumns

Please use deserialize json and select it as jarray in type argument…

Now use for loop with the output of deserialize json and …in for loop change type argument to jobject(if type argument is not present in properties then degrade system activities to 22.10 , ideaaly for most of the cases in latest type argument is choose automatically)

Now inside loop use add data row activity and give item array as {currentitem("Status"),Currentitem("StartProcessing"),Currentitem("CreationTime")}

Cheers

7

Worked Perfectly!

Thanks mate, I’m happy that it is solved now, marked as solution for sure.

Can you please have a look at the json response again, at the end there is something like this in each item :
“OrganizationUnitId”: ,
“OrganizationUnitFullyQualifiedName”: " @gmail.com”,
“Id”: 2222222,
“ProcessingException”: null,
“SpecificContent”: {
“Queue Name”: "Name “,
“Queue Folder”: “Folder”,
“Process Name”: "Process ”
},
it is something like, an array inside array,

How can I access or extract the Queue Name?

Thanks mate

8

@Tarek_Kariish

Its a nested jobject try like this currentitem("SpecificContent")("Queue Name")

Cheers

9

Dude, you are amazing, thank you very much

1 Like
10

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