ArangoDB array query for an array

Question

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"
      }
    **]**
  }
]

Please help as i'm struggling with this array for few days. tried with PUSH but no luck :( since retrieve query is not working, i am no where near update statement for this requirment — Rena76

CodeManX CodeManX · Accepted Answer · 2021-07-05T10:36:30

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].

ArangoDB array query for an array

1 Answers