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

Viewed 32

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.

0 Answers
Related