N1QL subset and superset

SQL++
1

Hello!

I have this document to a bucket b: {“data-v”: [“a”, “b”, “c”, “d”]} and I have this input: in = [“a”, “b”, “e”].

How can I search with a N1QL query if ‘in’ is subset of ‘data-v’ (and if ‘in’ is superset)?

Also, how can I search if some values of ‘in’ are included at ‘data-v’ (or all values of ‘in’ not included at ‘data-v’)?

Thanks!

2

If you have distinct elements in the two arrays, or that it doesn’t matter if duplicates in one match one in the other, then:

WITH a AS (["a","b","c","d"])
    ,b AS (["a","b","e"])
SELECT 
  EVERY x IN b SATISFIES ARRAY_CONTAINS(a,x) END -- b is a sub-set of a (or a is a super-set of b)
 ,EVERY x IN a SATISFIES ARRAY_CONTAINS(b,x) END -- a is a sub-set of b (or b is a super-set of a)
 ,ANY x IN b SATISFIES ARRAY_CONTAINS(a,x) END   -- at least one element of b is present in a
 ,ANY x IN a SATISFIES ARRAY_CONTAINS(b,x) END   -- at least one element of a is present in b
 ,EVERY x IN b SATISFIES NOT ARRAY_CONTAINS(a,x) END -- no element in b is present in a
;

You can also nest collection operators instead of using the ARRAY functions, e.g.

WITH a AS (["a","b","c","d"])
    ,b AS (["a","b","c"])
SELECT
  EVERY x IN b SATISFIES ANY y IN a SATISFIES y == x END END -- b is a sub-set of a (or a is a super-set of b)
;

You could also use the ARRAY functions and examine the result-set, e.g.

WITH a AS (["a","b","c","d"])
    ,b AS (["a","b","c"])
SELECT
 ARRAY_SORT(ARRAY_INTERSECT(a,b)) == ARRAY_SORT(b)  -- the intersection can be used too (sort for predictable stability)
;

(but this would probably only be worth it if you want/need the resulting array too).

I’m SELECTing the conditions here but of course you would use them in the WHERE clause for filtering.

e.g. (note the different “b” arrays)

cbq> WITH a AS (["a","b","c","d"])
   2     ,b AS (["a","b","c"])
   3 SELECT 
   4   EVERY x IN b SATISFIES ARRAY_CONTAINS(a,x) END -- b is a sub-set of a (or a is a super-set of b)
   5  ,EVERY x IN a SATISFIES ARRAY_CONTAINS(b,x) END -- a is a sub-set of b (or b is a super-set of a)
   6  ,ANY x IN b SATISFIES ARRAY_CONTAINS(a,x) END   -- at least one element of b is present in a
   7  ,ANY x IN a SATISFIES ARRAY_CONTAINS(b,x) END   -- at least one element of a is present in b
   8  ,ARRAY_SORT(ARRAY_INTERSECT(a,b))               -- the intersection can be used too (sort for predictable stability)
   9 ;
[
    {
        "$1": true,
        "$2": false,
        "$3": true,
        "$4": true,
        "$5": [
            "a",
            "b",
            "c"
        ]
    }
,   {
        "resultCount": 1,
        "status": "success"
    }
]
cbq> WITH a AS (["a","b","c","d"])
   2     ,b AS (["a","b","e"])
   3 SELECT 
   4   EVERY x IN b SATISFIES ARRAY_CONTAINS(a,x) END -- b is a sub-set of a (or a is a super-set of b)
   5  ,EVERY x IN a SATISFIES ARRAY_CONTAINS(b,x) END -- a is a sub-set of b (or b is a super-set of a)
   6  ,ANY x IN b SATISFIES ARRAY_CONTAINS(a,x) END   -- at least one element of b is present in a
   7  ,ANY x IN a SATISFIES ARRAY_CONTAINS(b,x) END   -- at least one element of a is present in b
   8  ,ARRAY_SORT(ARRAY_INTERSECT(a,b))               -- the intersection can be used too (sort for predictable stability)
   9 ;
[
    {
        "$1": false,
        "$2": false,
        "$3": true,
        "$4": true,
        "$5": [
            "a",
            "b"
        ]
    }
,   {
        "resultCount": 1,
        "status": "success"
    }
]

Ref:

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/arrayfun.html
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/collectionops.html

HTH.

1 Like