How to use nested array fields in a WHERE clause?

[RussellJ]

Hi

I’m making an API call that returns an array of invoices. Nested inside each invoice is an array of discounts.  Here’s a screengrab of the (cut down) Response Body for the step so you can visualise the structure…

It’s a snap (as US folks would say) to return the minimum created_at date from the invoices array…

… and this works as expected. It’s just that I want to return the minimum created_at date value from invoices which match a specific discount code value.

Feels like I need to use “where” but I can’t figure out how to refer to nested array fields in the where clause.  I'm after something like...

where("invoices.discounts.code" = myvalue)

I know how to use nested array values for "pluck", but can't see how the syntax works with "where".

What am I missing...?

Thanks in advance

Russell

[gary1 · Accepted solution]

Unfortunately this isn't possible using where. You can either use a bunch of loops, or you can dump it into a Ruby action and solve it quickly with .map or .select. I tried a few science projects to do it inline, but nothing was as efficient or convenient as a simple Ruby action.

[RussellJ]

Thanks Gary.  After I posted, one of the suggested related posts (with a reply by yourself) comes up - https://systematic.workato.com/t5/workato-pros-discussion-board/can-we-filter-a-complex-structure/td... ... in which you suggest code, so I've just started down that route.  Feels like a pain though for something so simple 😥

[gary1]

That post is 3 years old and I've come a long way since then! My example uses a .each loop. I used this a lot because it was easy to follow mentally, but using .select/map are really the most efficient ways to do it. Once I committed to learning how to use them properly, there was no turning back. You could also dial in ChatGPT or Claude to write the code snippets for you. I started doing this once I understood them well enough on my own.