CODEWITHSUNDEEP
arangodbaql
wener
wener

Reputation: 7750

In Arangodb how to do multi upsert in single for loop

I expected to run both upsert

for i in [1,2]
upsert { _key: i } insert {v:i} update{} in Test
upsert { _key: i*100 } insert {v:i*100} update{} in Test

But I got 

Query: variable '$OLD' is assigned multiple times (while parsing)

Version: 3.1.14

EDIT

In real query , what I want to do is something like this

FOR n IN MyData
FILTER n.person
    FOR p IN n.person
    UPSERT { _key: p.id } INSERT { _key:p.id, name:p.name} UPDATE {} IN Person
    UPSERT { _from:CONCAT('Person/',p.id),_to:CONCAT('Other/',n.id) }
    INSERT { _from:CONCAT('Person/',p.id),_to:CONCAT('Other/',n.id), type: n.type }  
    UPDATE {} IN PersonRelation

Have ever arangodb consider there will be multi upsert ?

Currently, I have to run query one by one, the dataset is big, so, it's very slow to just run one upsert a time. Maybe I should use INSERT and ignoreErrors.

Upvotes: 2

Views: 1627

Answers (1)

David Thomas
David Thomas

Reputation: 2349

I'm pretty sure this error is happening because you are writing to the collection twice and the query optimizer won't let you do it.

Another solution is to write the same content, still using an UPSERT command, but shuffle it around a bit so you are only writing once.

This example works: 

LET new_documents = FLATTEN(FOR i IN [1,2] RETURN [{_key: TO_STRING(i), v:i},{ _key: TO_STRING(i*100), v:i*100 } ])
FOR doc IN new_documents
    UPSERT { _key: doc._key } INSERT { _key: doc._key, v: doc.v } UPDATE{} IN Test
    RETURN {
        new_entries: NEW,
        old_entries: OLD
   }

A few notes:

  • A new_documents variable is created and it contains the records you want to add.
  • The results of new_documents goes through FLATTEN to turn it into a flat array of objects.
  • The _key of each document is converted to a string, as a _key must be a string
  • Then loop through that flattened array, and perform your UPSERT on each member of array
  • Access the output variables NEW and OLD which are free output variables from the UPSERT command.

The NEW variable can be very useful if you were inserting new records and the _key value was being generated automatically. By looking at the NEW variable you can fetch the _keys of all the new documents you just added, if you added them at all.

Update

To support your updated question, you can still perform multiple UPSERTs by isolating them in a variable definition, like this:

FOR n IN myData
FILTER n.v
    LET test = (UPSERT { _key: n.v } INSERT { _key: n.v, v: n.v } UPDATE {} IN Test)
    LET test2 = (UPSERT { _key: n.v } INSERT { _key: n.v, v: n.v } UPDATE {} IN Test2)
    RETURN {
        done: true
    }

This query reads documents from myData and then based on that performs UPSERT commands into the collections Test and Test2.

That should help you with your data model, just make sure the value you use as the _key for the new documents is a string.

Upvotes: 2

Related Questions