topic Re: Extracting from a nested collection generated from API call, JSON in Product Forum

Extracting from a nested collection generated from API call, JSON

Fran.Woodcock — Tue, 15 Oct 2024 14:55:06 GMT

Hi all

I'm trying to work with Freshdesk, a procurement solution, and they have CAPCHA at their sign on stage. So I have used an API to call all open tickets in my group resulting in the response content in JSON format. I have then used the Utility - JSON object to put it in to a collection.

(The Freshdesk Developer documentation won't support me adding in the subject as a parameter to the API call)

There seems to be 3 layers of collection to get to the actual data:

And now I'm having trouble extracting the rows in to a new collection so I can filter it. Advice has been use a calculation and loop which I've built but it only picks up one row and exits the loop. Other advice has been to use some custom code in the response but I don't have the expertise to write this, or get chatgpt to write it...)

I want to be able to filter by subject that starts with RFC to get the relevant tickets for the process and add them tot he queue, but I think I need to get it into a format that can be filtered first?

Current BP version = 6.10.5

Thanks

Fran

Re: Extracting from a nested collection generated from API call, JSON

murisf — Tue, 15 Oct 2024 16:12:33 GMT

You are looping over the Tickets collection which is just one row. Try pulling out Tickets.results into its own collection using a Calculate stage. That should give you a collection with the 17 rows you can loop over.

Re: Extracting from a nested collection generated from API call, JSON

Nandhakumar — Wed, 16 Oct 2024 05:18:19 GMT

@Fran.Woodcock ,

You can try, JSON Utility, where there is an action to skip JArray as well. Hope that helps.

Similar issue, pls checkout this thread(Answered: Re: Response Content to Collection - SS&C Blue Prism Community). it may help.

Thanks,

Nandha

Re: Extracting from a nested collection generated from API call, JSON

Tejaskumar_Darji — Wed, 16 Oct 2024 05:57:43 GMT

Take the inner collection to a new collection and then on top of it you can filter. Use loop on the main collection.

Re: Extracting from a nested collection generated from API call, JSON

Fran.Woodcock — Thu, 31 Oct 2024 12:56:54 GMT

I ended up having a KS session and we built a 3 loop solution. I'd never have worked it out myself.. not experienced enough! But I was on the right track, thanks all for your suggestions

Fran