我正在尝试使用AQL更新嵌入式数组中json文档上的属性。如何使用下面的AQL更新“家庭”类型地址的“地址栏”?

用户:

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

到目前为止的AQL尝试
for u in users
   for a in u.address
     FILTER a.type='home'
       UPDATE u WITH {<What goes here to update addressline?>} in users

感谢您的帮助。

问候,
安然

最佳答案

为此,我们必须使用临时变量。我们将在其中收集子列表并对其进行更改。我们选择一个简单的 bool 过滤条件以使查询更好地理解。

首先,让我们用一个样本创建一个集合:

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

这是将子列表保留在alteredList上以便稍后进行更新的查询:
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

虽然查询现在可以使用:
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"
      }
    ]
  }
]

该查询可能很快就会成为性能瓶颈,因为会修改集合中的所有文档,而不管值是否更改。因此,如果我们确实更改了文档的值,我们只希望对其进行更新。因此,我们使用第二个FOR来测试subList是否将被更改:
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

10-06 05:01