I am trying to write an update command in arangodb to insert an single "key":"value" attribute in document having nested array.
{
"OuterBlock": {
"InnerBlock": {
"key1": "value1",
"key2": {
"key21": "value21"
},
"key3": {
"key31": "value31"
},
"key4": [
{
"key41": "value1",
"key42": {
"key421": "value421"
},
"key43": [
{
"key431": "value431",
"key432": {
"key4321": "value4321"
}
}
]
},
{
"key44": "value44",
"key45": {
"key451": "key451"
}
}
]
}
}
}
I need to add one more key:value pair under key432 (example: "key4322":"value4322"). I tried with select query first and tried to add this attribute using MERGE command.
FOR t IN test
FILTER t._key=="Test"
Collect a = t.OuterBlock.InnerBlock.key4[0].key43[0].key432 into aitems
LET newa = (MERGE(a , {"key4322": "value4322"}))
RETURN newa
It returned result as below
[
{
"key4321": "value4321",
"key4322": "value4322"
}
]
so i tried merging this result with first block "key43" using below query
FOR t IN test
FILTER t._key=="Test"
collect a = t.OuterBlock.InnerBlock.key4[0].key43[0] into aitems
LET newa = (MERGE(a , {key432:
(
FOR t IN test
FILTER t._key=="Test"
Collect b = t.OuterBlock.InnerBlock.key4[0].key43[0].key432 into bitems
LET newb = (MERGE(b , {"key4322": "value4322"}))
Return newb
)
}))
RETURN newa
And the output is giving me an additional array block [] in key432 which is not there in the original data. hence it is changing the format of the document. How can i remove this array block. Please suggest.
[
{
"key431": "value431",
"key432": **[**
{
"key4321": "value4321",
"key4322": "value4322"
}
**]**
}
]
You need to replace array elements and merge objects step by step because variables in AQL are immutable. It would be easier to extend the nested object on the client-side and then replace the whole document on the server-side. It is possible in AQL, nonetheless:
FOR t IN test
FILTER t._key == "test"
LET key432 = MERGE(
t.OuterBlock.InnerBlock.key4[0].key43[0].key432,
{ key4322: "value4322" }
)
LET key43 = REPLACE_NTH(
t.OuterBlock.InnerBlock.key4[0].key43, 0,
MERGE(t.OuterBlock.InnerBlock.key4[0].key43[0], { key432 })
)
LET key4 = REPLACE_NTH(t.OuterBlock.InnerBlock.key4, 0,
MERGE(t.OuterBlock.InnerBlock.key4[0], { key43 })
)
RETURN MERGE_RECURSIVE(t, { OuterBlock: { InnerBlock: { key4 } } })
Result:
[
{
"OuterBlock": {
"InnerBlock": {
"key1": "value1",
"key2": {
"key21": "value21"
},
"key3": {
"key31": "value31"
},
"key4": [
{
"key41": "value1",
"key42": {
"key421": "value421"
},
"key43": [
{
"key431": "value431",
"key432": {
"key4321": "value4321",
"key4322": "value4322"
}
}
]
},
{
"key44": "value44",
"key45": {
"key451": "key451"
}
}
]
}
}
}
]
Regarding the extra array, keep in mind that subqueries always return an array just like the top-level query does.
LET newa = (MERGE(a , {key432:
(
FOR t IN test ... // subquery
)
To take just the first element returned by the subquery, you can do FIRST( FOR ... )
or ( FOR ... )[0]
.