How to deserialize Json

[sonaliaggarwal47 (Sonali Aggarwal)]

Hi @UiPath_Community,

I have below Json payload.

{
“listitemscount”: “2”,
“listitems”: “[\" 8080,2,Test,Two,712-222-1112,san2@test.com,Associations,Fall Region Meeting / Réunion du comité régional – anme,QC,1119-10-28,to the delegate / au délégué,122,fgv,1222,830.960000000000,dfd,4795188,Quebec,these are comments,952.960000000000","0fd4efc0 (2).jpg,https://abc.sharepoint.com/sites/Main_and_Sub/Lists/Dee Ep Form/Attachments/8080/0fd4efc0 (2).jpg","travel thoughts2.pdf,https://abc.sharepoint.com/sites/Main_and_Sub/Lists/Dee Ep Form/Attachments/8080/travel thoughts2.pdf",\" 9850,2,Kate,R,1138666061,kate_r@test.ca,Access Limited,Fall Region Meeting / Réunion du comité régional – anme,SK,1911-12-02,to the delegate / au délégué,100,100,fgv,0,830.960000,4711188,Alberta,200.000000000000","Christmas invite.pdf,https://abc.sharepoint.com/sites/Main_and_Sub/Lists/Dee Ep Form/Attachments/9850/Christmas invite.pdf","Mileage for region meeting travel.pdf,https://abc.sharepoint.com/sites/Main_and_Sub/Lists/Dee Ep Form/Attachments/9850/Mileage for region meeting travel.pdf"]”
}

I need to parse this payload and do below:

1. Run the outer loop for listitemscount
2. run the inner loop for attachmentscount in that loop. Attachments count is the second value under key - listitems. First one is list item
3. as per the attachments count, after column value 20, there are further columns which are dynamic. there will be 2 columns(display name of attachment, attachment url) per attachment. So, in this case, attachments count =2, hence, after column 20, there are 4 more columns.
4. i need to download those attachments in local.

Regards
Sonali

[Anil_G (Anil Gorthi)]

@sonaliaggarwal47

1. Deserialize json
2. Then for loop with enumerable.Range(0,cint(jobj("listitemscount)))
3. Inside loop jobj("listitem")(currentitem).ToString will give each separate list item for each iteration
4. As per screenshot loops like each item is string ans seprated by comma so you can split and loop on the array
5. For downloads you can check value starting with http and then use that url in download file from url activity as full url is present

Cheers

[arivu96 (Arivazhagan A)]

Hi @sonaliaggarwal47 ,

Please find the attached xaml file

Sequence2.xaml (7.6 KB)

Regrads,
Arivu

[ppr (Peter Preuss)]

@sonaliaggarwal47
It would be better if you share the JSON as a text file with us and check it beforehand with a validator (e.g. JSON Lint).

On this basis we would have a chance to prototype it optimized for compact extraction options.

Thanks for support

[sonaliaggarwal47 (Sonali Aggarwal)]

Hi @ppr,

PFA text file.

listattachments.json (1.0 KB)

Regards
Sonali

[ppr (Peter Preuss)]

Just to break it down:

At this part we can derive that the value type of listitems is string, holding a serialized String array inside
Visual:

Proof:

As we can see, that there is no formal marker between first or second item (as we have understood so far: first: 8080, second: 9850) we need to segment it by our logic

• e.g. Marker is the 4 digit

Maybe you can confirm, so far.

@sonaliaggarwal47

i need to download those attachments in local.

when it is just about attachment download, then we would recommend an approach like

• filter items on http occurrende and extract the url
• OR detect it by: items where the beginning is also at the end

Also have a look here:

JArray.Parse(myJObject("listitems").value(Of String)).Values(Of String).Where(Function (x) x.Contains("http")).Select(Function (x) x.Split(","c).Last()).ToArray()

which we can also shift to Query Syntax for a better readability

[sonaliaggarwal47 (Sonali Aggarwal)]

@ppr

As we can see, that there is no formal marker between first or second item (as we have understood so far: first: 8080, second: 9850) we need to segment it by our logic

• e.g. Marker is the 4 digit

I can have the json breakdown like attached, does this help?

list_item_att.txt (1.1 KB)

Regards
Sonali

[ppr (Peter Preuss)]

@sonaliaggarwal47
Have sent you a PM

[sonaliaggarwal47 (Sonali Aggarwal)]

Thanks @ppr for your help!

Below LINQ worked!

(From li In JArray.Parse(myJObject(“listitems”).tostring())
Let id = li.Value(Of String).Split(“,“c).First().Trim
Let lal = JArray.Parse(myJObject(“listitemattachments”).tostring()).Values(Of String).ToArray.Where(Function (x) x.contains(”/”+id+“/”)).toList()
Let t = Tuple.Create(id,lal)
Select r=t).ToDictionary(Function (x) x.Item1, Function (x) x.item2)

Regards
Sonali