How to use N1QL to match at least one keyword in keywords array?

SQL++
1

I want to input $content to get the tag by match at least one keyword in keywords base on the rule document.
for example,
input “some words include key1, and key2.” get:
{"subject":"s1","tag":["tagA"]}
input " "key1* with other words, maybe include key3." get:
{"subject":"s2","tag":["tagA","tagB"]}
input “some info without key.” get:
{"subject":"s3","tag":[]}

sample document:

INSERT INTO default VALUES("doc-1", {"subject":"s1","content":"some words include key1, and key2."});
INSERT INTO default VALUES("doc-2", {"subject":"s2","content":"key1 with other words, maybe include key3."});
INSERT INTO default VALUES("doc-3", {"subject":"s3","content":"some info without key."});
INSERT INTO default VALUES("rule-123", {"type":"rule","rules":[{"tag":"tagA","keywords":["key1","key2"]},{"tag":"tagB","keywords":["key3","key4"]}]});

notice : a) rule should get by document rule-123 ; b) content field is non-English language, so TOKENS() function can not use here.

2

I find the answer myself, FYI

SELECT ARRAY_FLATTEN(ARRAY (ARRAY_DISTINCT(ARRAY (rule.tag)
                                             FOR k IN rule.keywords
                                            WHEN CONTAINS(a.content,k) END 
                                          )
                           )
                       FOR rule IN ARRAY_FLATTEN(rules,1)
                       END,
                    1) AS tag , a.subject
  FROM default a
   LET rules = (SELECT RAW rules  FROM default USE KEYS ["rule-123"])
 WHERE META(a).id LIKE "doc-%"
3

Modified the your solution by Using FIRST.

SELECT ARRAY_DISTINCT(ARRAY (FIRST rule.tag FOR k IN rule.keywords WHEN CONTAINS(a.content,k) END )
                       FOR rule IN rules
                       END
                    ) AS tag , a.subject
  FROM default a
   LET rules = (SELECT RAW rules  FROM default USE KEYS ["rule-123"])[0]
 WHERE META(a).id LIKE "doc-%";
1 Like