Why is my postgres lateral subquery failing?

Question

I'm trying to run the following query with postgres's support for LATERAL subqueries:

with s as
    (
        select
            tagValues ->> 'mode' as metric
            , array_agg(id) as ids
        from
            metric_v3.v_series
        where
            name = 'node_cpu'
        group by 1
    )
select
    t.starttime
    , s.metric
    , t.max
from
    s, lateral (
        select
            d.starttime
            , max(d.max) as max
        from
            metric_v3.gaugedata d
        where
            d.starttime >= '2020-01-17T00:00Z' AND  d.starttime < '2020-01-24T00:00Z'
            and d.seriesid in s.ids
        group by 1
    ) t
order by 1,2;

It fails with, where s relates to the reference in the lateral subquery's where clause.

SQL Error [42601]: ERROR: syntax error at or near "s"

I have tried different methods for the lateral query, but I always get the same error. I don't know what I'm missing.

If I run the CTE expression and select s.* from it, I get the expected results, so that part is working fine.

I'm running Postgres 11.6 on CentOS.

Answer 1

You can't use IN with an array. You need to use the ANY operator:

and d.seriesid = any(s.ids)