How to correctly create array index

SQL++
1

I have the following sample document:

{
“_class”: “some.package.Class”,
“creationTime”: 1234567890,
“title”: “title of element”,
“tags”: [ “tag1”, “tag2”, “tag3” ]
}

I now want to query all elements which contain given 2 tags with:

SELECT * FROM bucket WHERE _class = “some.package.Class”
AND (EVERY t IN [“tag1”, “tag2”] SATISFIES t IN tags END) LIMIT 16

I tried to create an index for this query with:

CREATE INDEX some_idx_title ON bucket((distinct (array k for k in tags end))) WHERE (_class = “some.package.Class”)

But on explaining the select query it does not show up.

How do I have to create the index for this query?

2

In select query you need to use ANY or ANY AND EVERY. As Index doesn’t maintain MISSING on leading key EVERY can’t use array index.

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY

Range predicates (ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY) allow you to test a boolean condition over the elements or attributes of a collection or object(s). They each evaluate to a boolean value.

ANY or SOME is TRUE if the collection is non-empty and at least one element matches.

EVERY is TRUE if the collection is empty, or if the collection is non-empty and every element matches.

ANY AND EVERY or SOME AND EVERY is TRUE if the collection is non-empty and every element matches.

If you need to query array that contains “tag1” OR “tag2”, You should do the following query (As you are looking for more than one item is present at different array positions you need to use separate ANY clause)

SELECT * 
FROM bucket 
WHERE _class = “some.package.Class”
AND ANY t IN  tags SATISFIES t  IN ["tag1", "tag2"] END
 LIMIT 16

If you need to query array that contains both “tag1” AND “tag2”, You should do the following query

SELECT * 
FROM bucket 
WHERE _class = “some.package.Class”
AND ANY t IN  tags SATISFIES t  = "tag1" END 
AND ANY t IN  tags SATISFIES t  = "tag2" END
 LIMIT 16
2 Likes