How to find documents in ArangoDB which have a certain date (range) in an array?

Question

I am trying to find all documents within a collection that have events within a certain date range.

Simplified the document structure looks like this:

{
    "name": "Example document",
    "other": "property",
    "date_events": [
        {
            "start": "1963-10-12T00:00:00.000Z",
            "end": "1963-10-13T12:00:00.000Z"
        },
        {
            "start": "1970-04-20T00:00:00.000Z",
            "end": "1970-04-20T12:00:00.000Z"
        }
    ]
}

There are n documents, which all have the property date_events, which is an array of objects, containing n events, each event has start and end.

Now I want to find all documents which have events within a certain date range, eg. between 1970-04-10 and 1970-04-28:

FOR doc IN api_documents
    FILTER (doc.date_events[*].start ANY >= "1970-04-10T00:00:00.000Z" &&
        doc.date_events[*].end ANY <= "1970-04-28T12:00:00.000Z")
    RETURN { _key: doc._key, _id: doc._id, events: doc.date_events[*] }

The problem is, that the above query is incorrect and the result contains also documents that have no matching events whatsoever, eg. this one:

{
    "name": "False positive",
    "other": "property",
    "date_events": [
        {
            "start": "1966-02-24T00:00:00.000Z",
            "end": "1966-02-24T12:00:00.000Z"
        },
        {
            "start": "1979-11-26T00:00:00.000Z",
            "end": "1979-11-30T12:00:00.000Z"
        },
        {
            "start": "1980-01-31T00:00:00.000Z",
            "end": "1980-01-31T12:00:00.000Z"
        }
    ]
}

I can get it to work when only filtering for one property, eg. start. But as soon as both start and end is in the query, all the queries that I was able to come up with, either produce zero results or false positives, which do not match the input date range.

Is the usage of the ANY operator incorrectly here when looking for two properties or what am I doing wrong?

Thank you.

Answer 1

Your current query returns every document that includes date_events that fit either criterion.

To make sure both criteria are matched by a single date_event, you can filter date_events in a sub-query and then filter for non empty results, something like this (untested, but should get you started):

FOR doc IN api_documents
    LET matches = (
        FOR de IN doc.date_events
            FILTER de.start >= "1970-04-10T00:00:00.000Z" &&
                   de.end <= "1970-04-28T12:00:00.000Z"
            RETURN 1
    )
    FILTER LENGTH(matches) > 0
    RETURN { _key: doc._key, _id: doc._id, events: doc.date_events }