ARRAY_DISTINCT keep order

n1ql

#1

I have documents like these:

{
	"type": "TYPE_1",
	"array": [
		{
			"field": "A"
		},
		{
			"field": "B"
		},
		{
			"field": "C"
		}
	]
}

Where every documents of TYPE_1 has the same ordering (A, B, C) but with some exceptions where their sometime “bis fields” like this:

{
	"type": "TYPE_1",
	"array": [
		{
			"field": "A"
		},
		{
			"field": "A_BIS"
		},
		{
			"field": "B"
		},
		{
			"field": "C"
		}
	]
}

I would like to get an array of all the fields but by keeping the same ordering.
I manage to get all the distinct fields but this is not keeping the order:

SELECT ARRAY_DISTINCT(ARRAY_FLATTEN(ARRAY_AGG(fields), 2)) FROM bucket
LET fields = ARRAY item.field FOR item IN array END

If I’m using only ARRAY_FLATTEN the ordering is kept but I then have all the duplicates. Any idea on how to solve this?


#2
SELECT DISTINCT RAW d FROM ARRAY_FLATTEN((SELECT RAW ARRAY_AGG(fields) FROM default
                         LET fields = ARRAY item.field FOR item IN `array` END),3) AS d;

#3

This is working latest version (v6.0.0) but it throws an error on the community edition (v5.1.1):

 {
    "code": 5010,
    "msg": "Error evaluating ExpressionScan. - cause: FROM in correlated subquery must have USE KEYS clause: FROM default"
  }