topic Re: How to use nested array fields in a WHERE clause? in Workato Pros Discussion Board

How to use nested array fields in a WHERE clause?

RussellJ — Wed, 13 May 2026 12:47:08 GMT

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

Re: How to use nested array fields in a WHERE clause?

gary1 — Wed, 13 May 2026 16:16:05 GMT

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.

Re: How to use nested array fields in a WHERE clause?

RussellJ — Wed, 13 May 2026 16:20:55 GMT

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-p/5777 ... in which you suggest code, so I've just started down that route. Feels like a pain though for something so simple 😥

Re: How to use nested array fields in a WHERE clause?

gary1 — Wed, 13 May 2026 16:40:21 GMT

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.