{"id":2987,"date":"2017-03-17T08:50:45","date_gmt":"2017-03-17T15:50:45","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=2987"},"modified":"2025-06-13T18:46:21","modified_gmt":"2025-06-14T01:46:21","slug":"feature-enhancements-n1ql-couchbase-server-5-march-developer","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/feature-enhancements-n1ql-couchbase-server-5-march-developer\/","title":{"rendered":"Feature Enhancements to N1QL in the Couchbase Server 5.0 March Developer Build"},"content":{"rendered":"

With the recent March 2017 Developer build of Couchbase there were many bug fixes, but also feature enhancements to core technologies like N1QL. \u00a0For example, now indexes can be created on various meta information such as the document cas and expiration values. \u00a0Another example includes a simplified syntax when creating array indexes.<\/p>\n

We’re going to take a look at some of these enhancements and how you might use them in your own application.<\/p>\n

Simplified Array Indexing in Couchbase 5.0<\/h2>\n

With Couchbase Server 4.5 came the array indexing. Take the following sample document for example:<\/p>\n

{\r\n  \"type\": \"person\",\r\n  \"firstname\": \"Nic\",\r\n  \"lastname\": \"Raboy\",\r\n  \"social-media\": [\r\n    {\r\n      \"type\": \"twitter\",\r\n      \"url\": \"https:\/\/www.twitter.com\/nraboy\"\r\n    },\r\n    {\r\n      \"type\": \"website\",\r\n      \"url\": \"https:\/\/www.thepolyglotdeveloper.com\"\r\n    }\r\n  ]\r\n}<\/pre>\n
New Array Index Syntax<\/h3>\n
Before Couchbase 5.0, to index the array elements found in social-media<\/code> you had to write an index that looked something like the following:<\/p>\n
CREATE INDEX ism \r\nON `default` ( DISTINCT ARRAY media FOR media IN `social-media` END )\r\nWHERE type = 'person';<\/pre>\n
In the above example, the\u00a0FOR<\/code> operator was necessary for array indexing. \u00a0In Couchbase Server 5.0 there is a much more simplified syntax. \u00a0The same index can be created via the following:<\/p>\n
CREATE INDEX ism \r\nON `default` ( DISTINCT `social-media` )\r\nWHERE type = \"person\";<\/pre>\n
To make sure your index works, you can execute the following N1QL query:<\/p>\n
EXPLAIN SELECT *\r\nFROM default\r\nWHERE type = 'person' AND ANY media IN `social-media` SATISFIES media.type = 'website' END;<\/pre>\n
When looking through the results of the\u00a0EXPLAIN<\/code> you should see that it is using the\u00a0ism<\/code> index that was previously created.<\/p>\n
Now just because the simplified syntax exists doesn’t mean you can’t use the previous syntax when array indexing. \u00a0The following would be a perfect example of why the previous syntax would still be valid:<\/p>\n
CREATE INDEX ism_website\r\nON `default` ( DISTINCT ARRAY media FOR media IN `social-media` WHEN media.type = 'website' END )\r\nWHERE type = 'person';<\/pre>\n
Notice that a\u00a0WHEN<\/code> operator was used when creating the\u00a0ism_website<\/code> index above.<\/p>\n
More information on array indexing can be found\u00a0here<\/a>, along with other useful documentation on creating indexes in Couchbase.<\/p>\n
Relaxed Variable Match Requirement for Array Indexes<\/h3>\n
In 4.x releases, the array indexing required usage of exactly same variable names in the SELECT<\/code> query that were\u00a0used in the CREATE INDEX<\/code> statement. Find more details here<\/a>.<\/p>\n
For example, referring to the previous queries seen\u00a0above, note that the variable media<\/code> that is used to iterate through the array social-media<\/code> is very important. Array indexing in 4.x mandates the exact variable name media<\/code> to be used in the previous\u00a0SELECT<\/code> query.<\/p>\n
Couchbase 5.0 release relaxes this requirement, and following query would work perfectly fine:<\/p>\n
EXPLAIN SELECT *\r\nFROM default\r\nUSE INDEX (ism)\r\nWHERE type = 'person' AND ANY m IN `social-media` SATISFIES m.type = 'website' END;<\/pre>\n
Note that, the index ism<\/code> uses variable name media<\/code>, but the query above\u00a0uses m<\/code>. Still, the above query\u00a0can use the index ism<\/code> successfully.<\/p>\n
A Relaxed Whole Array Index Key Requirement for Array Indexes<\/h3>\n
Also recall, in 4.x releases, the covered array indexing requires the whole array attribute as a mandatory index-key in the array index definition. \u00a0For example, take the following query again:<\/p>\n
EXPLAIN SELECT *\r\nFROM default\r\nWHERE type = 'person' AND ANY media IN `social-media` SATISFIES media.type = 'website' END;<\/pre>\n
The covered array index corresponding to the above query would be:<\/p>\n
CREATE INDEX ism_covered \r\nON `default` ( DISTINCT ARRAY media FOR media IN `social-media` END,  `social-media`)\r\nWHERE type = 'person';<\/pre>\n
Note that, the second index key social-media<\/code> is mandatory, for example to cover following query:<\/p>\n
EXPLAIN SELECT media\r\nFROM default\r\nWHERE type = 'person' AND ANY media IN `social-media` SATISFIES media IS NOT NULL END;<\/pre>\n
In Couchbase 5.0, this same query will be covered by index ism<\/code> at the very start of this article.<\/p>\n
It is important to note that, this feature brings lot of power to Array Indexes. Because, now each entry in the array index consumes less storage space and memory. It enables following benefits:<\/p>\n