AQL return flat merged list

Question

I want to return a unique list of product options in AQL, I understand COLLECT can return unique list however, how do I now flatten it into an array.

So I want to return the following filtered list: (lets call it list [A])

[
  [
    "Size"
  ],
  [
    "Size",
    "Color"
  ],
  [
    "value"
  ]
]

as: (lets call this list [B])

["Size","Color","Value"]

Query used to obtain list [A]

FOR product IN products
    COLLECT options = product.options[*].option
    FILTER LENGTH( options ) > 0
RETURN options

I tried FLATTEN, UNIQUE and no luck. Perhaps am not using functions accurately. My initial thought would be to repack items in list [A] to create list [B] something like push [A] into [B] if not in [B]

stj · Answer 1 · 2015-09-25T19:49:58.907

4

FLATTEN should actually work. By default it will collapse items on the first level only, but it can be given an extra argument for the number of levels to collapse.

For example, you can use it with level 3 on a more deeply-nested structure like this:

/* values to flatten */
LET values = [[[["Size"]],[["Size","Color"]],["value"]]]
RETURN FLATTEN(values, 3)

This will return all items and sub-items in a flat array, i.e.

[ 
  "Size", 
  "Size", 
  "Color", 
  "value" 
]

In the specific example query you posted, using FLATTEN like as follows will not work, because FLATTEN would be invoked individually for each product document:

FOR product IN products
  COLLECT options = product.options[*].option
  FILTER LENGTH( options ) > 0
  RETURN FLATTEN(options, 2)

So it won't produce a single, collapsed array, but multiple, already collapsed arrays of depth 1.

To create a collapsed array from all product documents, the FLATTEN can be applied outside of the FOR loop:

RETURN FLATTEN(
  FOR product IN products
    COLLECT options = product.options[*].option
    FILTER LENGTH( options ) > 0
    RETURN options
)

edited Sep 25 '15 at 19:49

answered Sep 25 '15 at 11:17

stj

9,037
19
33

FOR product IN products COLLECT options = product.options[*].option FILTER LENGTH( options ) > 0 RETURN FLATTEN( options ) or RETURN FLATTEN( options , 3 ) returns the same results – isawk Sep 25 '15 at 11:20
Noticed example array you used is not similar to output I have [["Size"],["Size","Color"], ["value"]] – isawk Sep 25 '15 at 11:23
I used an even more nested array as an example use case for `FLATTEN`. It will work with any number of nesting levels. – stj Sep 25 '15 at 19:45
The difference and why it does not work for the specific query is that `FLATTEN` will work on an array, but the query you're using is producing one array per `product` document found. So there'll be multiple invocations of `FLATTEN`, each on an individual array, so `FLATTEN` has no chance to collapse them into a single array. I'll update my answer so it takes into account gathering the data from such collection-based query. – stj Sep 25 '15 at 19:46

AQL return flat merged list

1 Answers1