ArangoDB AQL: updating a single object in an inline array

Question

I am trying to update an attribute in a json document in an inline array using AQL. How to update the address bar for the address "home" using AQL below?

User:

{ name: "test", address: [ {"addressline": "1234 superway", type:"home"}, {"addressline": "5678 superway", type:"work"} ] } 

AQL attempt so far

 for u in users for a in u.address FILTER a.type='home' UPDATE u WITH {<What goes here to update addressline?>} in users 

Thanks for the help.

Regards, Anjan

Answer 1

To do this, we need to work with temporary variables. We will collect a sublist there and change it. We choose the condition of a simple Boolean filter to make the query more understandable.

First, create a collection with a sample:

 database = db._create('complexCollection') database.save({ "topLevelAttribute" : "a", "subList" : [ { "attributeToAlter" : "oldValue", "filterByMe" : true }, { "attributeToAlter" : "moreOldValues", "filterByMe" : true }, { "attributeToAlter" : "unchangedValue", "filterByMe" : false } ] }) 

Heres the Query, which saves the sublist in the altertered file to update it later:

 FOR document in complexCollection LET alteredList = ( FOR element IN document.subList LET newItem = (! element.filterByMe ? element : MERGE(element, { attributeToAlter: "shiny New Value" })) RETURN newItem) UPDATE document WITH { subList: alteredList } IN complexCollection 

As long as the request is, it now works:

 db.complexCollection.toArray() [ { "_id" : "complexCollection/392671569467", "_key" : "392671569467", "_rev" : "392799430203", "topLevelAttribute" : "a", "subList" : [ { "filterByMe" : true, "attributeToAlter" : "shiny New Value" }, { "filterByMe" : true, "attributeToAlter" : "shiny New Value" }, { "filterByMe" : false, "attributeToAlter" : "unchangedValue" } ] } ] 

This query is likely to become a performance bottleneck soon, as it modifies all the documents in the collection whether the values ​​are changed or not . Therefore, we only want to UPDATE documents if we really change their cost. Therefore, we use the second FOR to check whether the sublist will be changed or not:

 FOR document in complexCollection LET willUpdateDocument = ( FOR element IN document.subList FILTER element.filterByMe LIMIT 1 RETURN 1) FILTER LENGTH(willUpdateDocument) > 0 LET alteredList = ( FOR element IN document.subList LET newItem = (! element.filterByMe ? element : MERGE(element, { attributeToAlter: "shiny New Value" })) RETURN newItem) UPDATE document WITH { subList: alteredList } IN complexCollection 

Answer 2

ArangoDB now supports subset indexes. The following query is based on dothebart :

 FOR document IN complexCollection FILTER document.subList[*].filterByMe == true LIMIT 1 LET alteredList = ( FOR element IN document.subList LET newItem = (element.filterByMe == true ? MERGE(element, { attributeToAlter: "the shiniest value"})) : element RETURN newItem) UPDATE document WITH { items:alteredList } IN complexCollection 

Note. Remember to create a hash index on subList[*].filterByMe :

 db._collection('complexCollection') .ensureIndex({type:'hash',fields:['subList[*].filterByMe']});