Indexing array of strings

SQL++
1

Hello,

I’m trying to improve performance through indexation.
I have items with an array containing keywords:

article: {
  name: "Name",
  price: 42.00,
  infos: {
    keywords: ["key1", "key2", "key3"]
  }
}

I want to optimize the next query: SELECT * FROM articles AS a WHERE ANY k IN a.infos.keywords SATISFIES k == “keyword” END;
I tried to indexing this keywords array with CREATE INDEX idx_keywords ON articles (DISTINCT ARRAY k FOR k IN infos.keywords END);

But Couchbase (Enterprise Edition 5.0.0 build 3519 - with Docker) doesn’t use this index when I run the query. What am I missing ?

Thanks for your help.

2

Seems right. Please post the explain.

The bucket is articles. and document shows article. Is the root of document is starts with article you may have to include in the path.

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

3

My bucket is “articles” and content is:

<UUID>: {
  name: "Name",
  price: 42.00,
  infos: {
    keywords: ["key1", "key2", "key3"]
  }
}

Actually I have read the doc already but every example shows array with objects [{name:“item”, value:2.0}, …], so I can’t found an example with the same kind of data.
And you maybe already guess but english ain’t my first language, I’m afraid to miss something in the doc.

Here the Explains for CREATE INDEX idx_keywords ON articles (DISTINCT ARRAY k FOR k IN infos.keywords END);:

{
  "plan": {
    "#operator": "CreateIndex",
    "index": "idx_keywords",
    "keys": [
      {
        "expr": "(distinct (array `k` for `k` in (`infos`.`keywords`) end))"
      }
    ],
    "keyspace": "articles",
    "namespace": "default",
    "using": "default"
  },
  "text": "CREATE INDEX `idx_keywords` ON `articles` (DISTINCT ARRAY k FOR k IN infos.keywords END);"
}

…and the Explain for SELECT * FROM articles AS a WHERE ANY k IN a.infos.keywords SATISFIES k == “key1” END; (I dropped the PRIMARY INDEX, to be sure it uses the right index, but I can rebuild it if you want)

[
  {
    "code": 4000,
    "msg": "No index available on keyspace articles that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.",
    "query_from_user": "SELECT * FROM articles AS a WHERE ANY k IN a.infos.keywords SATISFIES k == \"key1\" END;"
  }
]
4

Surprised you are getting error. Can you check if index is created and ONLINE. You can also post output of the

select * from system:indexes WHERE name = "idx_keywords";

5

Hi and thanks for your help.

I tried your query and see that the index was in state “building”. In fact, when I created my server, I used the option “Memory-optimized” and, on 3’000’000 elements, I think I had a huge lack of memory to build the index completely (stuck at 15%).

Anyway, I re-created a server with the index storage mode “Standard Global Secondary” : index fully builded and queries drastically optimized (from 4m30s to 4s).

Thanks a lot.