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
– Rena76, Commented Jul 2, 2021 at 11:51

CodeManX · Accepted Answer · 2021-07-05 10:36:30Z

0

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

answered Jul 5, 2021 at 10:36

CodeManX

12.1k5 gold badges55 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rena76 Over a year ago

Thanks a lot for your reply. I will check this query. One doubt - can you explain this line little more - "It would be easier to extend the nested object on the client-side and then replace the whole document on the server-side."

CodeManX Over a year ago

AQL does not let you change variables in place, but you can do that in e.g. JavaScript like doc.OuterBlock.InnerBlock.key4[0].key43[0].key432.key4322 = "value4322". So modifying the data structure becomes easier. Then you need to replace the original document with the modified one, e.g. in AQL with REPLACE { ... } IN test. You could implement this on the client-side with arangojs, but you could also write your own AQL function in JavaScript that returns the modified document: arangodb.com/docs/stable/aql/extending.html

Rena76 Over a year ago

i cant use replace_nth as i'm using lower version of arango database

CodeManX Over a year ago

As it's the first element in both cases, you can use UNSHIFT(SHIFT(t.OuterBlock.InnerBlock.key4[0].key43), MERGE(...)) and UNSHIFT(SHIFT(t.OuterBlock.InnerBlock.key4), MERGE(...)) instead. A generic solution for any position would be (FOR i IN 0..LENGTH(<array>)-1 RETURN i == <pos> ? <new-value> : <array>[i]).

CodeManX Over a year ago

Note that REPLACE_NTH() was introduced in v3.7.0 and v3.6 support ends next month (end of August 2021).

|

Collectives™ on Stack Overflow

ArangoDB array query for an array

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related