Searching from array (complex criteria) without unnesting? I have table with arrays of dates. How to search in them?

create table if not exists somedates (
startdate date,
otherdates date[]);

insert into somedates (startdate, otherdates) values
('2000-11-14', '{2001-10-30}'),
('2001-09-26', '{2002-09-20,2004-09-21,2006-09-19,2010-09-17}'),
('2005-05-10', '{2006-08-03,2008-08-12}'),
('2004-09-29', '{2005-09-29,2006-10-02,2007-10-04,2009-09-22,2010-08-06,2011-09-01,2013-09-17}');


I would want to find all rows, where in otherdates there is "1 year" (0.6 – 1.4) AND "5 year" (3.5 – 5.5) followup-dates found.

Following doesn't work:

SELECT * 
FROM somedates
WHERE any(otherdates) - startdate &gt; 0.6*365
      and any(otherdates) - startdate &lt; 1.4*365
      and any(otherdates) - startdate &gt; 3.5*365
      and any(otherdates) - startdate &lt; 5.5*365
;


Following works, but if there is (and there is in the real case) lots of dates, I should put lots more OR-cases.

select * from
    (SELECT *, generate_subscripts(otherdates, 1) as s
    FROM somedates) as foo 
where (otherdates[s] - startdate) &gt;= 0.6*365
    and (otherdates[s] - startdate) &lt;= 1.4*365
    and (
           ((otherdates[s+1] - startdate) &gt;= 3.5*365
           and (otherdates[s+1] - startdate) &lt;= 5.5*365)
        or ((otherdates[s+2] - startdate) &gt;= 3.5*365
           and (otherdates[s+2] - startdate) &lt;= 5.5*365)
        or ((otherdates[s+3] - startdate) &gt;= 3.5*365
           and (otherdates[s+3] - startdate) &lt;= 5.5*365))        
;


2001-09-26   {'2002-09-20','2004-09-21','2006-09-19','2010-09-17'}   1
2004-09-29   {'2005-09-29','2006-10-02','2007-10-04','2009-09-22','2010-08-06','2011-09-01','2013-09-17'}   1


Using some kind of ANY-structure somehow would make more sense than needing to enumerate all indexes.

I know by unnesting I can do the search also, but surely there should be a way to do this without unnesting?
Even if the syntax were valid, your first query wouldn't do what you describe anyway, as you need to ensure it is same date from the array which is > one thing and &lt; another thing, not two different dates.  You need to use ranges, both to be syntactically valid, and to be semantically valid.

select * from somedates where 
    daterange((startdate+interval'0.6 years')::date,(startdate+interval'1.4 years')::date) @&gt; ANY (otherdates) and
    daterange((startdate+interval'3.5 years')::date,(startdate+interval'5.5 years')::date) @&gt; ANY (otherdates);


You can make it a bit less ugly like this:

create or replace function daterange2(date, interval, interval) returns daterange 
  as $$ select daterange(($1+$2)::date, ($1+$3)::date) $$
  language sql immutable parallel safe;

select * from somedates where                                                                                             
    daterange2(startdate,'0.6 years','1.4 years') @&gt; ANY (otherdates) and
    daterange2(startdate,'3.5 years','5.5 years') @&gt; ANY (otherdates);