[SOLVED] Is it abe to unite two columns of one document into one column in ArangoDB query?

Is it abe to unite two columns of one document into one column in ArangoDB query?

I have an AQL query:

return union_distinct(
    for a in table
        return a.Id,
    for b in table
        return b.Id)

Is it able to get two columns as one array in one loop?

Solution

Yes. It's possible the following way:

RETURN UNIQUE(FLATTEN(
  FOR doc IN collection
  RETURN [doc.colA,doc.colB]
))

The FOR loop generates a two-dimensional array: [[doc1.colA,doc1.colB],[doc2.colA,doc2.colB], etc...] This needs to be flattened, then then unique values filtered out. See the documentation of array function: https://docs.arangodb.com/3.11/aql/functions/array/

The result of this query will be a nested array: [[unique values...]], so you just need to get the first element of the result array to get to the list you want.

Another solution which will give you a non-nested array:

FOR elem IN (FLATTEN(
  FOR doc IN collection
  RETURN [doc.colA,doc.colB]
)
RETURN DISTINCT elem

Although this is two loops again, but the second one runs on an in-memory array, so it's quite fast.

Note: The "DISTINCT" here is not a function but a special form of RETURN: https://docs.arangodb.com/3.11/aql/high-level-operations/return/#return-distinct