What is the proper way to use IF THEN in AQL?

Question

I'm trying to use IF THEN style AQL, but the only relevant operator I could find in the AQL documentation was the ternary operator. I tried to add IF THEN syntax to my already working AQL but it gives syntax errors no matter what I try.

LET doc = DOCUMENT('xp/a-b')
LET now = DATE_NOW()
doc == null || now - doc.last >= 45e3 ? 
  LET mult = (doc == null || now - doc.last >= 6e5 ? 1 : doc.multiplier)
  LET gained = FLOOR((RAND() * 3 + 3) * mult)
  UPSERT {_key: 'a-b'}
  INSERT {
    amount: gained,
    total: gained,
    multiplier: 1.1,
    last: now
  }
  UPDATE {
    amount: doc.amount + gained,
    total: doc.total + gained,
    multiplier: (mult < 4 ? FLOOR((mult + 0.1) * 10) / 10 : 4),
    last: now
  }
  IN xp
  RETURN NEW
 : 
  RETURN null

Gives the following error message:

stacktrace: ArangoError: AQL: syntax error, unexpected identifier near 'doc == null || now - doc.last >=...' at position 1:51 (while parsing)

If the syntax error is pointing to a specific location and you don't report that in your question, how do you expect us to give a targetted response? Could you edit the question and copy paste into it the exact error message along with any surrounding context it gives? — Ed Grimm, Feb 05 '19 at 03:35
There is no surrounding context, but I will edit the question. — ElJay, Feb 05 '19 at 03:47
Um, OMG, I totally missed your first ternary... which was really bad of me because it was clearly the point of all of this. If this will work at all, you'll need to parenthesize the entire thing between the first `?` and its matching `:` because there's no way precedence rules would let you do that without help. However, I'm much more inclined to think that is something that they expect you to use the associated arangojs language to do that logic. — Ed Grimm, Feb 05 '19 at 04:00
Just seems odd to me that a language meant to be similar to SQL would have no way to do IF THEN like you could with SQL. That's a pretty heavy blow to the language's usability. — ElJay, Feb 05 '19 at 04:08

CodeManX · Accepted Answer · 2019-02-05T19:32:21.303

The ternary operator can not be used like an if/else construct in the way to tried. It is for conditional (sub-)expressions like you use to calculate mult. It can not stand by itself, there is nothing it can be returned or assigned to if you write it like an if-expression.

Moreover, it would require braces, but the actual problem is that the body contains operations like LET, UPSERT and RETURN. These are language constructs which can not be used inside of expressions.

If I understand correctly, you want to:

insert a new document if no document with key a-b exists yet in collection xb
if it does exist, then update it, but only if the last update was 45 seconds or longer ago

Does the following query work for you?

FOR id IN [ 'xp/a-b' ]
    LET doc = DOCUMENT(id)
    LET key = PARSE_IDENTIFIER(id).key
    LET now = DATE_NOW()
    FILTER doc == null || now - doc.last >= 45e3
    LET mult = (doc == null || now - doc.last >= 6e5 ? 1 : doc.multiplier)
    LET gained = FLOOR((RAND() * 3 + 3) * mult)
    UPSERT { _key: key }
    INSERT {
        _key: key,
        amount: gained,
        total: gained,
        multiplier: 1.1,
        last: now
    }
    UPDATE {
        amount: doc.amount + gained,
        total: doc.total + gained,
        multiplier: (mult < 4 ? FLOOR((mult + 0.1) * 10) / 10 : 4),
        last: now
    }
    IN xp
    RETURN NEW

I added _key to INSERT, otherwise the document will get an auto-generated key, which does not seem intended. Using a FOR loop and a FILTER acts like an IF construct (without ELSE). Because this is a data modification query, it is not necessary to explicitly RETURN anything and in your original query you RETURN null for the ELSE case anyway. While yours would result in [ null ], mine produces [ ] (truly empty result) if you try execute the query in quick succession and nothing gets updated or inserted.

Note that it is necessary to use PARSE_IDENTIFIER() to get the key from the document ID string and do INSERT { _key: key }. With INSERT { _key: doc._key } you would run into an invalid document key error in the insert case, because if there is no document xp/a-b, DOCUMENT() returns null and doc._key is therefore also null, leading to _key: null - which is invalid.

Corrected, and added more details why I added `PARSE_IDENTIFIER()`. — CodeManX, Feb 05 '19 at 19:32
Actually, the document key is the sole input to this, that's my bad for being vague but the above AQL was me trying to get the query working in arangosh before I used it with arangojs. The collection name is also preset, so I can safely do this without anything like `PARSE_IDENTIFIER()`. Thanks for the help! — ElJay, Feb 06 '19 at 00:06

What is the proper way to use IF THEN in AQL?

1 Answers1