How to get first and last entries in an ArangoDB AQL query

Question

I need help with an ArangoDB AQL query. I have a transaction detail collection (EventTran) that logs update details on its parent table (Event). EventTran attributes include a timestamp, and a reference to the parent _id_event. I’m trying to work out a query to return an array of only the first and last (by timestamp) EventTran documents for a specified id_event. Here's an example:

FOR event IN EventTran
    FILTER event._id_event == "Event/167697"
    SORT event.timestamp DESC
    RETURN event

Might return:

[
  {
    "_key": "214092",
    "_id": "EventTran/214092",
    "_id_event": "Event/167697",
    "timestamp": 1511202637
  },
  {
    "_key": "213958",
    "_id": "EventTran/213958",
    "_id_event": "Event/167697",
    "timestamp": 1511202542
  },
  {
    "_key": "191809",
    "_id": "EventTran/191809",
    "_id_event": "Event/167697",
    "timestamp": 1511118705
  },
  {
    "_key": "167701",
    "_id": "EventTran/167701",
    "_id_event": "Event/167697",
    "timestamp": 1510965562
  }
]

I want a query that will return an array with only the first and last items, i.e. the very first log entry, and the most recent log entry:

[
  {
    "_key": "214092",
    "_id": "EventTran/214092",
    "_id_event": "Event/167697",
    "timestamp": 1511202637
  },
  {
    "_key": "167701",
    "_id": "EventTran/167701",
    "_id_event": "Event/167697",
    "timestamp": 1510965562
  }
]

Answer 1

The following is undoubtedly not the best possible solution in all circumstances, but it does avoid SORT, which is probably the last thing you want to do unless the collection is quite small.

The idea is very simple: determine the min and max values, and then collect the minimal items and select one of them, and likewise for the maximal items.

LET mnmx = (
  FOR x in EventTran
  FILTER event._id_event == "Event/167697"
  COLLECT AGGREGATE mn = MIN(x.timestamp), mx = MAX(x.timestamp)
  RETURN {mn,mx} )

LET mn = mnmx.mn
LET mx = mnmx.mx

LET least = (
  FOR x in EventTran
  FILTER x.timestamp == mn
  COLLECT y=x INTO minimal
  RETURN minimal[0] )

LET greatest = (
  FOR x in EventTran
  FILTER x.timestamp == mx
  COLLECT y=x INTO maximal
  RETURN maximal[0] )

RETURN {least, greatest}

The last line is an abbreviation for {"least": least, "greatest": greatest}, least and greatest being the items with the least and greatest timestamps.