ArangoDB AQL: Update single object in embedded array

Question

I am trying to update the attribute on a json document in an embedded array using AQL. How do i update the "addressline" for "home" type address 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

Thank you for the help.

Regards, Anjan

RienNeVaPlu͢s · Accepted Answer · 2016-11-26 03:38:58Z

To do this we have to work with temporary variables. We will collect the sublist in there and alter it. We choose a simple boolean filter condition to make the query better comprehensible.

First lets 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 keeps the subList on alteredList 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

While the query as it is is now functional:

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 will probably be soonish a performance bottleneck, since it modifies all documents in the collection regardless whether the values change or not. Therefore we want to only UPDATE the documents if we really change their value. Therefore we employ a second FOR to test whether subList will be altered 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

Thanks @dothebart! This is great! I knew that the entire sublist might have to be passed in the update/merge but was struggling with setting up that sublist. Thanks again.

RienNeVaPlu͢s · Accepted Answer · 2020-05-09 14:16:22Z

9

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

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

Note: Don't forget to create a hash index on subList[*].filterByMe:

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

edited May 9, 2020 at 14:16

answered Nov 26, 2016 at 5:02

RienNeVaPlu͢s

7,7026 gold badges46 silver badges79 bronze badges

Collectives™ on Stack Overflow

ArangoDB AQL: Update single object in embedded array

User:

AQL Attempt so far

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

User:

AQL Attempt so far

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related