Looping 2 queries


I’m loooking the way to loop a query whith the result of another query,
For example, I have this query wich give me the hours in a day SELECT DATE_RANGE_STR(“2020-02-26T00:00:00+01:00”, “2020-02-27T00:00:00+01:00”, ‘hour’)
and I have another query doing something like: ´Select * from …. where timeStamp < “2020-02-26T12:00:00+01:00"”
I want to run the second query 24 times (1 per hour in a day, resulting from the other query).
I tried something like SELECT ARRAY v2 FOR v1 IN (SELECT DATE_RANGE_STR(“2020-02-26T00:00:00+01:00”, “2020-02-27T00:00:00+01:00”, ‘hour’) ), v2 IN (Select * from …. where timeStamp < v1 ) END
and it’s not working, the second query just run 1 time.
Thanks a lot in advance and regards.

ARRAY (SELECT * FROM default WHERE timeStamp < ts ) 
  FOR ts IN DATE_RANGE_STR("2020-02-26T00:00:00+01:00", "2020-02-27T00:00:00+01:00", "hour") END

I am not sure if you want above one you are doing 24 times scan same data again.

Following uses one time scan into array then iterate and builds the info you want

SELECT ARRAY (SELECT RAW a FROM avs AS a  WHERE a.timeStamp < ts )
  FOR ts IN DATE_RANGE_STR("2020-02-26T00:00:00+01:00", "2020-02-27T00:00:00+01:00", "hour") END
LET avs = (SELECT RAW b FROM mybucket AS b WHERE b.timeStamp < "2020-02-27T00:00:00+01:00");

The following gives documents every hour and you if need use all previous once.
NOTE: As it aggregates all documents into array it may need huge memory.

SELECT dayhour, ARRAY_AGG(d) AS docs
FROM default AS d
LET dayhour = DATE_TRUNC_STR(timeStamp,"hour")
WHERE timeStamp  >= "2020-02-26T00:00:00+01:00" AND timeStamp < "2020-02-26T00:00:00+01:00"
GROUP BY dayhour
ORDER BY dayhour;