Array indexing and query

SQL++
1

Hi All,

I did try searching the forum for answers before posting. I have following objects stored in CB,

k1 → {
tags: [“red”, “green”, “blue”]
}

k2 → {
tags: [“purple”, “red”]
}

I want to query for all tags that contain “red”. Here the answer is “k1, k2”.

What is the best create index statement and the best query statement?

Thanks,
Neome Web

2

I created an index using,

CREATE INDEX TestTableQuery_tags_type ON TestDb.TestTableQuery.TestCollection (type,DISTINCT tags) WITH {‘defer_build’:true}

but the below query does not work,

SELECT COUNT(*) AS count FROM TestDb.TestTableQuery.TestCollection WHERE (type = “TestTableQuery” AND CONTAINS (tags, “RED”))

3

found the solution, instead of CONTAINS in the select statement above, I had to use ARRAY_CONTAINS

However, is this the right way of doing this…

4 
CREATE INDEX `TestTableQuery_tags_type` ON `TestDb` . `TestTableQuery` . `TestCollection` ( DISTINCT `tags` ) 
WHERE type = "TestTableQuery";

SELECT COUNT(1) AS count 
FROM `TestDb` . `TestTableQuery` . `TestCollection`  AS t
WHERE t.type = "TestTableQuery" AND  ANY tag IN tags SATISFIES  tag = "red" END;

https://index-advisor.couchbase.com/indexadvisor/#1

1 Like
5

Thanks! That worked!!