ArangoDB / AQL Update Nested Documents

Given the following document structure:

{
  "_key": "abc",
  "_id": "abc",
  "label": "Company ABC",
  "departments": [
    {
      "name": "Department 123",
      "id": "123"
    },
    {
      "name": "Department 456",
      "id": "456"
    }
  ]
}

Can you tell me why the following query doesn't work? The error message is "missing document key". I found a much longer more convoluted workaround in another SO (ArangoDB AQL: Update single object in embedded array) but I'm curious as to what exactly is wrong with the simpler query.

FOR c IN company
  FOR d in c.deparments
    FILTER d.id == “456”
    UPDATE d WITH { name: “Department 789” } IN company
RETURN d

Solution

Currently, I only know how to update top level attributes in ArangoDB. While I hope there are alternatives brought to my attention in the future, here are some options for your scenario.

Update with MERGE

In this example, we update the top level departments attribute by rewriting the array and using MERGE function when our condition is hit.

LET company = DOCUMENT("companies/abc")

UPDATE company WITH {
  departments:
   (FOR department IN company.departments
     RETURN department.id == "456" ?
       MERGE(department, {name: "Department 789"}) : department)
} IN companies

Using an alternative data model

The above example assumes department data is embedded in company documents. Consider the following:

"companies" collection

{
  "_key": "abc",
  "_id": "companies/abc",
  "label": "Company ABC"
}

"departments" collection (separate docs)

{
  "_key": "456",
  "_id": "departments/456",
  "company": "companies/abc",
  "name": "Department 456"
}

{
  "_key": "123",
  "_id": "departments/123",
  "company": "companies/abc",
  "name": "Department 123"
}

Getting Company #ABC with Departments:

LET company = DOCUMENT("companies/abc")

LET departments =
  (FOR department IN departments
    FILTER department.company == company._id
    RETURN department)

RETURN MERGE(company, {departments})

Results:

{
  "_id": "companies/abc",
  "_key": "abc",
  "label": "Company ABC",
  "departments": [
    {
      "_key": "456",
      "_id": "departments/456",
      "company": "companies/abc",
      "name": "Department 456"
    },
    {
      "_key": "123",
      "_id": "departments/123",
      "company": "companies/abc",
      "name": "Department 123"
    }
  ]
}

Updating Department #456:

LET department = DOCUMENT("departments/456")

UPDATE department WITH {name: "Department 789"} IN departments

Note that you'll want to add an index on the company attribute in the departments collection. You can get details on indexing here:

https://docs.arangodb.com/3.11/index-and-search/indexing/which-index-to-use-when/

You'll have to weigh the pros and cons of the two models.