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.

Have you tried using UPSERT as described in the ArangoDB documentation? Is there any specific problem? Can you add more details about what kind of query did you use and what error do you get? — Peter Liapin
– Peter Liapin, Commented Mar 10, 2019 at 16:06
No problem if i use single row insert, i'm try to use UPSERT with multiple rows insert. as sample code in question, if i use sql it may look like this: ` INSERT INTO table(name, value) VALUES('a', '1'), ('b', 2), ('c', 4) ON DUPLICATE KEY UPDATE name="value" ` — Ata amini
– Ata amini, Commented Mar 10, 2019 at 17:06

CodeManX · Accepted Answer · 2023-09-26 10:50:24Z

9

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.

edited Sep 26, 2023 at 10:50

CodeManX

12.1k5 gold badges55 silver badges74 bronze badges

answered Mar 10, 2019 at 16:01

Peter Liapin

1,27512 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ata amini Over a year ago

Thanks for your reply, arangodb upsert documnet is about single row insert. if i try to batch insert so have to repeat upsert on each insert UPSERT{} INSERT{} UPDATE{}, in ordert to prevent multiple calling upsert i have to batch insert.

Peter Liapin Over a year ago

Batch processing is also possible with UPSERT. Please check my updated answer above.

Ata amini Over a year ago

Thanks Peter, this is the right way to achieve my desired result.

Collectives™ on Stack Overflow

Arangodb insert multiple rows and using UPSERT

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related