What is the best way to search array in docs if they match

N1QL
#1

I have a Doc which a user can categorize as well as assign it to one or more buckets. What is the most effective way to search if any doc contains any of the values provided for one of these arrays.

For example categories Array looks like this categories: [ “CC”, “DL”, “HL”, “R” ] and Bucket array looks like this
bucket : [“Seller”, “Buyer”, “Investor”]

in my query ifor example would want to get all docs where categories contains DL and HL in array as well as bucket is Seller or Buyer

#2

Use ARRAY indexing

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/indexing-arrays.html

As given index can have single ARRAY key, you need to choose one only other you apply post scan.

CREATE INDEX ix1 ON default (DISTINCT ARRAY v FOR v IN `bucket` END, `categories`) WHERE type = "doc";

SELECT t1.*
FROM (
                SELECT RAW META(d).id 
               FROM default  AS d
               WHERE  d.type = "doc"
                               AND ANY v IN d.`bucket` SATISFIES v IN ["Seller", "Buyer"] END 
                              AND "DL" IN d.categories 
                              AND "HL" IN  d.categories 
              ) AS d1 
JOIN default AS t1 ON KEYS d1;

Make sure subquery covered, if not directly use subquery.

#3

Ok i gave that a shot but something must be off here is what i used

SELECT t.*
FROM (SELECT RAW META(d).id 
           FROM Contacts  AS d
           WHERE  type = "doc"
                          AND ANY v FOR IN d.`bucket` SATISFIES v IN ["Seller", "Buyer"] END 
                          AND "DL" IN d.categories 
                          AND "HL" IN  d.categories 
          ) AS d1 
JOIN Contacts AS t1 ON KEYS d1;

which creates the flowing error

[{ "code": 3000, "msg": "syntax error - at FOR",
"query_from_user": "SELECT t.* FROM (SELECT RAW META(d).id                
FROM Contacts  AS d  WHERE  type = \"doc\" AND ANY v FOR IN d.`bucket` 
SATISFIES v IN [\"Seller\", \"Buyer\"] END  AND \"DL\" IN d.categories  
AND \"HL\" IN  d.categories  ) AS d1 JOIN Contacts AS t1 ON KEYS d1;"}]
#4

CREATE INDEX ix1 ON Contacts (DISTINCT ARRAY v FOR v IN bucket END, categories) WHERE type = “user”;

SELECT t1.*
FROM (SELECT RAW META(d).id 
           FROM Contacts  AS d
           WHERE  d.type = "user"
                          AND ANY v IN d.`bucket` SATISFIES v IN ["Seller", "Buyer"] END 
                          AND "DL" IN d.categories 
                          AND "HL" IN  d.categories 
          ) AS d1 
JOIN Contacts AS t1 ON KEYS d1;
#5

Still complains about the
“code”: 3000,
“msg”: “syntax error - at FOR”,

if i remove the “AND ANY v FOR IN d.bucket SATISFIES v IN [“Seller”, “Buyer”] END” the query runs fine

#6

try again. updated statement

#7

Thanks that did the trick is there a wild card char to use in the IN clause to return any match ? Trying to figure out if i
can place a wild card char in the in clause if user did not select a filter on the group or if i have to change the complete query.