Arangodb insert multiple rows and using UPSERT

Question

how do i insert multiple rows in arangodb with UPSERT? Collection contains a unique index which prevent insert duplicate documents. multiple insert work fine without unique index, but how can i handle update/replace in multiple insert with unique index?

like this:

INSERT [{doc1},{doc2},{doc3}]
IN collection

UPDATE {} // when duplicate per document

---

Update 1

SQL look like this:

INSERT INTO table(name, value)
VALUES('a', '1'), ('b', 2), ('c', 3)

ON DUPLICATE KEY UPDATE name=`value`

thanks.

Answer 1

ArangoDB supports UPSERT operation: https://docs.arangodb.com/3.11/aql/high-level-operations/upsert/

From ArangoDB documentation:

When using the UPDATE variant of the upsert operation, the found document will be partially updated, meaning only the attributes specified in updateExpression will be updated or added. When using the REPLACE variant of upsert, existing documents will be replaced with the contexts of updateExpression.

You can use UPSERT to update/replace/insert multiple records as following:

Let's insert few sample documents into your collection with the unique hash index for name attribute first:

FOR doc in [
    { "name": "Doc 1", "value": 1 }, 
    { "name": "Doc 2", "value": 1 }, 
    { "name": "Doc 3", "value": 1 }]

INSERT doc IN collection

Now if you want to perform a batch upsert you can run the following AQL:

FOR doc in [
    { "name": "Doc 2", "value": 2 }, 
    { "name": "Doc 3", "value": 2 },
    { "name": "Doc 4", "value": 1 }
]

UPSERT { "name": doc.name }

INSERT doc

UPDATE { "value": doc.value } in collection

AQL query above inserts one new Doc 4 document and updates value attribute for the Doc 2 and Doc 3.