Array symm diff with repeating values

SQL++
1

Hi,

I have 2 arrays that contains non-unique values. For example: ["A", "B", "B", "C"] and ["A", "A", "A", "B", "C", "D"].

If I use the ARRAY_SYMDIFF function I only get ["D"] but what I’m searching for is something that will take the number of occurence of a single value into account and will give me this result: ["A", "A", "B", "D"].

How can I achieve this?

Thanks.

2

Not sure what basis output is expected.I can’t think of any function in the N1QL.

One option will be Union the arrays and Unnest and do some grouping. Doing in application might be easier.

3

To explain a bit more the expected result, based on the same example as above:

  • Their is one “A” in the first array and 3 into the second one, so the diff is 2 “A”.
  • Their is 2 “B” in the first array and only one into the second one, so the diff is 1 “B”.
  • Their is 0 “D” in the first array and one into the second one, so the diff is 1 “D”.
  • “C” is present the same number of occurence into the first and second array so it doesn’t show up into the diff.
4

There is no simple function. Doing in application is easy.
The following is SQL gives expected results.

SELECT ARRAY_FLATTEN((SELECT RAW ARRAY t FOR t1 IN ARRAY_RANGE(0,ABS(ARRAY_LENGTH(a1)-ARRAY_LENGTH(a2))) END
        FROM ARRAY_UNION(a,b) AS t
        LET a1 = ARRAY v FOR v IN a WHEN t = v END, a2 = ARRAY v FOR v IN b WHEN t = v END
       ),3)
LET a = ["A", "B", "B", "C"], b= ["A", "A", "A", "B", "C", "D"];
5

Thank you. This is working fine on CB 5.x. But because ARRAY_UNION is not available on v4.5 on which I need to ensure compatibility, do you know any equivalent of this function?

I know I could do it in application in an easy way but I don’t really have a hand on what the client will do so it’s better for me if I manage to directly get the wanted result. And this “diff” is only a part of a bigger query.

6

ARRAY_DISTINCT(ARRAY_CONCAT(a,b))

7

Thanks. I tought the reason why it doesn’t work on 4.5 was because of that function but I still have an issue…
syntax error - at (
Do you have any idea why your request doesn’t work on 4.5?

8

Subquery expression is supported in 4.6+ only. Try this.

FROM ARRAY_UNION(a,b) AS t

===>

FROM (SELECT 1) AS p UNNEST ARRAY_DISTINCT(ARRAY_CONCAT(a,b)) AS t

9

By replacing those statements I got an empty array instead of the expected result. I presume this is because t is now referring to an item of the union instead of the full array? But I didn’t managed to get it working…