Creating an Index

SQL++
1

Hi all, I am a NIQL newbie and am struggling to create an index for gender. When I create an index with the syntax:
CREATE INDEX gender_ID ON sandbox(gender)
the index is created successfully but no items are displayed on the Index summary page. I include a screenshot of the structure of a document in my bucket

NIQL_index_problem
NIQL_index_problem637×711 130 KB

Any help would be greatly appreciated!

Thanks

A

2

gender is not top filed in the document. Index field must be referenced with path from top of the document. It is part of ARRAY entry, If you need index array element you must use ARRAY indexing. https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

CREATE INDEX  ix1  ON  `sandbox` ( DISTINCT ARRAY g.resource.gender FOR g IN entry END ) WHER type = "searchset";
SELECT *
FROM sandbox 
WHERE type = "searchset" AND ANY g IN entry SATISFIES g.resource.gender = "male" END;
3

Thanks very much, this is working well!

4

https://index-advisor.couchbase.com/

1 Like