Array indexing and query

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?

Neome Web

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”))

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…

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

FROM `TestDb` . `TestTableQuery` . `TestCollection`  AS t
WHERE t.type = "TestTableQuery" AND  ANY tag IN tags SATISFIES  tag = "red" END;

1 Like

Thanks! That worked!!