How to return all the elements of the first array, except for those which are also included in the second array

SQL++
,
1

I have two documents with arrays

a: {
  "rsIds": [
    "aaa",
    "bbb",
    "ccc",
    "ddd"
  ]
}

and

b:{
  "rsIds": [
    "ccc",
    "ddd",
    "eee",
    "fff"
  ]
}

I would like to have query returning all the elements of the first array, except for those which are also included in the second array, i.e.

{
  "rsIds": [
    "aaa",
    "bbb"
  ]
}

What I tried to do

SELECT ARRAY_EXCEPT(e1,e2)
    LET e1 = (SELECT RAW rsIds
    FROM catalog USE KEYS ("a")), e2 = (SELECT RAW rsIds
    FROM catalog USE KEYS ("b"));

But I still get all elements from the first array

Any advise on how could I fix the query?

2

Never mind, it works when ARRAY_FLATTEN applied to ARRAY_EXCEPT arguments

SELECT ARRAY_EXCEPT(ARRAY_FLATTEN(e1,1),ARRAY_FLATTEN(e2,1))
    LET e1 = (SELECT RAW rsIds
    FROM catalog USE KEYS ("a")), e2 = (SELECT RAW rsIds
    FROM catalog USE KEYS ("b"));
1 Like
3

As each separate key

WITH e1 AS ((SELECT RAW rsIds FROM catalog USE KEYS "a")[0])
     e2 AS ((SELECT RAW rsIds FROM catalog USE KEYS "b")[0])
SELECT ARRAY_EXCEPT(e1,e2) AS e;