arangodb

Is it possible to insert only if query returns no results

I am wanting to create an edge document only if one doesn't already exist with a number of values.

An example edge is

    {
      "_from": "person/x",
      "_to": "organisation/y",
      "startTime": "2022-01-01T00:00:00Z",
      "endTime": null,
      "role": "roles/z"
    }

An example filter would be

    FILTER doc._from == "person/x"
    FILTER doc._to == "organisation/y"
    FILTER doc.role == "roles/z"
    FILTER doc.startTime < DATE_ISO8601(DATE_NOW())
    FILTER (IS_NULL(doc.endTime) || doc.endTime > DATE_ISO8601(DATE_NOW()))

(not the less than/greater than FILTERs)

I only want to insert a new edge document if the filter returns no results

I have seen there is the UPSERT|INSERT|UPDATE operator, but that won't allow me to do the startTime/endTime checks.

Solution

  • You can do something like this:

    LET numEdges = FIRST(FOR doc IN edges
    FILTER doc._from == "person/x"
    FILTER doc._to == "organisation/y"
    FILTER doc.role == "roles/z"
    FILTER doc.startTime < DATE_ISO8601(DATE_NOW())
    FILTER (IS_NULL(doc.endTime) || doc.endTime > DATE_ISO8601(DATE_NOW()))
    COLLECT WITH COUNT INTO cnt
    RETURN cnt)
FILTER numEdges == 0
INSERT {...} INTO edges

    This uses a subquery to count the number of documents that match your filter, and then uses a separate filter to only perform the insert if we have not found any matching documents. However, you should note that there is still a potential race, because nothing prevents a matching document to be inserted while this query is executed.