AQL: Dynamic query with nested array dates

Question

I have been trying to get this dynamic query to work with dates as shown below in ArangoDB 3.1.

This works perfectly when I'm not trying to query dates, but returns an empty list as soon as I try to query with a date like below...

{ 
    query: 
        'For c IN @@collectionName 
        FILTER  (  c.@p0 == @v0  AND  c.@p1 >= @v1  AND  c.@p2 <= @v2  )  
        LIMIT  @count  RETURN c ',

    bindVars: {
        '@collectionName': 'Event',
        p0: 'isPublished',
        v0: true,
        p1: 'dates[*].startsAt',
        v1: '2018-06-01T04:00:00.000Z',
        p2: 'dates[*].startsAt',
        v2: '2018-07-01T03:59:59.999Z',
        count: 9
    } 
}

Need some help getting past this

Is there a reason that you store your dates in that format? Dates like that are just strings, and you're using the string comparator, as a date isn't a data type. If you convert all dates to Unix Epoch time at UTC 0 (with millisecond accuracy) then you're dealing with integers, and date comparisons become much easier. You'll need to build functions to determine Unix Epoch Time integers for given dates, but once done it's simpler. — David Thomas, May 14 '18 at 12:11

score 2 · Answer 1 · answered May 14 '18 at 13:20

There are mistakes in your query, but they are actually not related to dates:

dates[*].startsAt is not a valid attribute path, but a shorthand expression for FOR date IN dates RETURN date.startsAt, which returns an array
The comparison operator >= does not work on arrays as you may think. null, true, false and every number and string are less than any array, see Type and Value Order. Your timestamp array will always be greater than any given timestamp string. What you probably want instead is an array comparison operator like ALL >=.
An expression dates[*].startsAt can not be used as bind parameter. With a document structure without array like { "date": { "startsAt": "..." } } it would be perfectly fine to bind ["date", "startsAt"] as p1 or p2. Note how the bind parameter value is an array of strings. "date.startsAt" on the other hand would describe the path for a top-level attribute
{ "date.startsAt": ... } and not a nested attribute startsAt of the top-level attribute date like { "date": { "startsAt": ... } }.
What your do with dates[*].startsAt is describing a top-level attribute like
{ "dates[*].startsAt": ... }, which does not exist. ["dates[*]", "startsAt"] does not work either. If you want to use the array expansion expression, then you have to write it like c.@p1a[*].@p1b in your query and use the bind parameters { "p1a": "dates", "p2a": "startsAt" }.

Query:

FOR c IN @@collectionName 
  FILTER c.@p0 == @v0
  FILTER c.@p1a[*].@p1b ALL >= @v1 
  FILTER c.@p2a[*].@p2b ALL < @v2
  LIMIT @count
  RETURN c

bindVars:

{
  "@collectionName": "Event",
  "p0": "isPublished",
  "v0": true,
  "p1a": "dates",
  "p1b": "startsAt",
  "v1": "2018-06-01T04:00:00.000Z",
  "p2a": "dates",
  "p2b": "startsAt",
  "v2": "2018-07-01T04:00:00.000Z",
  "count": 9
}

AQL: Dynamic query with nested array dates

1 Answers1