我正在尝试使用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