I have a large number of documents that represent all the history of some tracked documents.
I have one bucket with the latest/live version, and another one for histories. I use another bucket so I can use cheaper machines to store this massive amount of data, which is not accessed often.

What I want to do is : for a set of document, get the version the closest to a date.

Example :
I have documents D1 to Dn , in today’s version. I would like to have the version of those documents 3 months ago. Of course there is no modification exactly 3 months ago, so I would like to know what was the live version 3 months ago.

Right now the only option I see is one request with sort & limit for each tracked document (in a form of a JOIN or a subquery), but I don’t see that as a sustainable solution performance wise.

This feels like a NEST with a sort & limit but afaik this cannot be performed through a nest clause.

Anyone knows better ?

NEST forms ARRAY. You can use subquery expression on the ARRAY do filter/sort/limit. NEST needs to materialize all the documents first.