I want to insert values into an Arango-DB with Custom Keys, but I didn't found a 'if not exists' function like at SQL
let exist = (
FOR u IN User
FILTER u._key == "newkey"
return u
)
Filter LENGTH(exist)==null
INSERT{
_key: "newkey",
value1: "value"
} IN User
return NEW
Something like this also doesn't work
INSERT{
_key: "newkey",
value1: "value"
}
IN User ON DUPLICATE KEY IGNORE
return NEW
How about looking at the UPSERT command, and only give it instructions for the INSERT part of the command and nothing for UPDATE. That way it would only execute if it didn't exist.
https://docs.arangodb.com/3.11/aql/high-level-operations/upsert/
Here is an example of it being used:
UPSERT {
username: @username
}
INSERT {
username: @username,
fullname: @fullname,
verified: false
}
UPDATE {
}
IN Users
RETURN {
user: NEW
}
The UPSERT command has three sections to it.
The first is the comparitor, it's how it identifies if a document exists or not. In this case the comparitor is { username: @username }. It uses this to search the Users collection to see if there is a match or not.
If there IS NOT a match, it performs the INSERT command.
If there IS a match, it performs the UPDATE command.
In this case we have been tricky and not provided anything for the UPDATE command, so it won't update anything if it exists, it will only insert if it doesn't exist.
The INSERT block then shows the keys/values that it inserts if the record is not found.
In this case, it is also setting verified to false because any new user that has just been created is not verified. It will be the responsibility of another command elsewhere in the app to convert this flag to true.
Also notice the RETURN entry, it references NEW and you can also reference a thing called OLD if needed.
These are internal return values provided by an UPSERT command and they reference two versions of the document that was referenced.
NEW is always populated, it contains the latest version of the document, regardless if an update was made or not.
OLD contains the old version of the document, before it was updated with the new values. If the document is not updated then it returns null, so in this case it will always be null as updates don't happen.
And yes, this executes exactly as an IF NOT EXISTS style command in other database languages. If the comparator doesn't match, it inserts the new record, if the comparator matches, it does nothing!
I hope that helps!
LET existingUser = FIRST(
FOR u IN User
FILTER u._key == "newkey"
return u
)
LET newUser = FIRST(
FILTER existingUser == null
INSERT{
_key: "newkey",
value1: "value"
} IN User
RETURN NEW
)
RETURN existingUser || newUser
This returns the existing user or inserts a user if it doesn't exist. The user object is then returned.
There are no unnecessary write operations
