Re: Why does this array query fail?

Thanks again David. I think that's all making sense to me now, except I
want to follow up on your last point:

Yes, un-nesting can make the problem go away though it too is unusual. For the
> most part either use relations/sets or use arrays (for a specific
> component of the schema). Your example mixes the two which makes using
> that part of the schema difficult.

I'm not sure exactly what you're saying here, but it's important to me
because I've recently had to do a lot with arrays, and continue to have to
do so. What I'm working with is similar to the example I gave you, but let
me be more concrete.

I'm working with a social service agency. Every time they see a client
they fill out an "encounter" record. Part of what is tracked is what kind
of services were provided, which is stored as an array of "service codes"
within the encounter. The encounter also has a date.

So I frequently have to provide information like "what were all the types
of services this client received during the last quarter?" or "show me all
the clients who received service X last year." I've learned enough to use
ANY, array_agg and unnest to get through these queries, but if I'm going
about this wrong or there's a better way to do it I'd love to know about it!

Ken