ArangoDB AQL Query - merge children into parent

Question

I have a simple parent-child relationship in ArangoDB. Each parent document can have zero to many children. Let's say the parent document has attributes pa1, and the child docs have one attribute ca1, and a reference back to the parent _id of "_id_parent". How do I write an AQL query to return a result set like:

    [{
            "_key": "111",
            "_id": "parent/111",
            "pa1": "aaa",
            "children": [{
                    "_key": "21",
                    "_id": "child/21",
                    "_id_parent": "parent/111",
                    "ca1": "www"
                },
                {
                    "_key": "22",
                    "_id": "child/22",
                    "_id_parent": "parent/111",
                    "ca1": "xxx"
                }
            ]
        },
        {
            "_key": "222",
            "_id": "parent/222",
            "pa1": "ddd",
            "children": [{
                    "_key": "31",
                    "_id": "child/31",
                    "_id_parent": "parent/222",
                    "ca1": "yyy"
                },
                {
                    "_key": "32",
                    "_id": "child/32",
                    "_id_parent": "parent/222",
                    "ca1": "zzz"
                }
            ]
        }
    ]

In other words, how do I "flatten" this:

    FOR p IN Parent
    FILTER p.pa1 == @parm1
    LET children = (
        (FOR c IN Child
            FILTER c._id_parent == p._id
            RETURN c)
    )
    RETURN {p, children}

CodeManX CodeManX · Accepted Answer · 2018-01-09T19:25:22

All what's left to do is to actually merge the children with the parent document:

RETURN MERGE(p, {children})

That is the short form of RETURN MERGE(p, {children: children}).

p is this:

{
  "_id": "Parent/111",
  "_key": "111",
  "_rev": "_WLsUlK2--_",
  "pa1": "aaa"
}

{children} or the more verbose {children: children} creates an object with a single attribute, with the name children and the value of the children variable:

{
  "children": [
    {
      "_key": "21",
      "_id": "Child/21",
      "_rev": "_WLsW4Su--_",
      "_id_parent": "Parent/111",
      "ca1": "www"
    },
    {
      "_key": "22",
      "_id": "Child/22",
      "_rev": "_WLsW8Zu--_",
      "_id_parent": "Parent/111",
      "ca1": "xxx"
    }
  ]
}

MERGE() combines both objects. Since there is no overlap in attribute keys ("_id", "_key", "_rev", "pa1" vs. "children"), no top level attributes of p are replaced by children.

BTW: You can spare one pair of parentheses around the subquery expression
LET var = ( ( <subquery> ) ) - it is sufficient to do LET var = ( <subquery> ).

ArangoDB AQL Query - merge children into parent

1 Answers