Adaptive index for whole sub-object

SQL++
,
1

I’m trying to create an Adaptive Index from one field of a document. i.e.:

 {
	"name": "some name",
	"description": "some description",
	"tags": {
		"a": ["tag1", "tag2", "tag3"],
		"b": ["tag4", "tag5"]
	},
	"type": "doc"
}

I’d like to create the following index:

CREATE INDEX docs_idx ON `docs-bucket`(DISTINCT PAIRS(tags)) WHERE `type`="doc";

So I could make the following query to retrieve that document:

SELECT *
FROM `docs-bucket` doc
USE INDEX (docs_idx)
WHERE type="doc" AND
EVERY input IN ["tag1", "tag2"] SATISFIES ANY t IN doc.tags.a SATISFIES input = t END END AND
EVERY input IN ["tag4", "tag5"] SATISFIES ANY t IN doc.tags.b SATISFIES input = t END END

However I get the following error:

“No index available on keyspace docs-bucket that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”

If I index self, then the query works:

CREATE INDEX docs_idx ON `docs-bucket`(DISTINCT PAIRS(self)) WHERE `type`="doc";

But in my case, this would be an overkill. Only what’s inside tags needs to be indexed, regardless of how the contents are organized. So, like self but only in tags.
Am I doing something wrong or I misunderstood Adaptive Indexes?

2

any fields that referenced in Index WHERE clause must also be part of the index keys.
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/adaptive-indexing.html

CREATE INDEX docs_idx ON `docs-bucket`
(DISTINCT PAIRS({tags,type})) WHERE `type`="doc";
1 Like
3

That was the problem, it works now.
Thank you @vsr1