DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Data
  4. Couchbase 5.0 Dev Build: Feature Enhancements to N1QL

Couchbase 5.0 Dev Build: Feature Enhancements to N1QL

Couchbase 5.0's latest dev build hopes to enhance N1QL features such as simplified array indexing and relaxed variable matching.

Nic Raboy user avatar by
Nic Raboy
·
Mar. 26, 17 · Tutorial
Like (1)
Save
Tweet
Share
3.11K Views

Join the DZone community and get the full member experience.

Join For Free

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

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

Simplified Array Indexing in Couchbase 5.0

With Couchbase Server 4.5 came the array indexing. Take the following sample document for example:

{
  "type": "person",
  "firstname": "Nic",
  "lastname": "Raboy",
  "social-media": [
    {
      "type": "twitter",
      "url": "https://www.twitter.com/nraboy"
    },
    {
      "type": "website",
      "url": "https://www.thepolyglotdeveloper.com"
    }
  ]
}


New Array Index Syntax

Before Couchbase 5.0, to index the array elements found in social-media you had to write an index that looked something like the following:

CREATE INDEX ism 
ON `default` ( DISTINCT ARRAY media FOR media IN `social-media` END )
WHERE type = 'person';


In the above example, the FOR operator was necessary for array indexing. In Couchbase Server 5.0 there is a much more simplified syntax. The same index can be created via the following:

CREATE INDEX ism 
ON `default` ( DISTINCT `social-media` )
WHERE type = "person";


To make sure your index works, you can execute the following N1QL query:

EXPLAIN SELECT *
FROM default
WHERE type = 'person' AND ANY media IN `social-media` SATISFIES media.type = 'website' END;


When looking through the results of the EXPLAIN you should see that it is using the ismindex that was previously created.

Now just because the simplified syntax exists doesn’t mean you can’t use the previous syntax when array indexing. The following would be a perfect example of why the previous syntax would still be valid:

CREATE INDEX ism_website
ON `default` ( DISTINCT ARRAY media FOR media IN `social-media` WHEN media.type = 'website' END )
WHERE type = 'person';


Notice that a WHEN operator was used when creating the ism_website index above.

More information on array indexing can be found here, along with other useful documentation on creating indexes in Couchbase.

Relaxed Variable Match Requirement for Array Indexes

In 4.x releases, the array indexing required usage of exactly same variable names in the SELECT query that were used in the CREATE INDEX statement. Find more details here.

For example, referring to the previous queries seen above, note that the variable media, which is used to iterate through the array social-media , is very important. Array indexing in 4.x mandates the exact variable name media to be used in the previous SELECT query.

The Couchbase 5.0 release relaxes this requirement, and the following query would work perfectly fine:

EXPLAIN SELECT *
FROM default
USE INDEX (ism)
WHERE type = 'person' AND ANY m IN `social-media` SATISFIES m.type = 'website' END;


Note that, the index ism uses the variable name media, but the query above uses m. Still, the above query can use the index ism successfully.

A Relaxed Whole Array Index Key Requirement for Array Indexes

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. For example, take the following query again:

EXPLAIN SELECT *
FROM default
WHERE type = 'person' AND ANY media IN `social-media` SATISFIES media.type = 'website' END;


The covered array index corresponding to the above query would be:

CREATE INDEX ism_covered 
ON `default` ( DISTINCT ARRAY media FOR media IN `social-media` END,  `social-media`)
WHERE type = 'person';


Note that, the second index key social-media is mandatory, for example to cover following query:

EXPLAIN SELECT media
FROM default
WHERE type = 'person' AND ANY media IN `social-media` SATISFIES media IS NOT NULL END;


In Couchbase 5.0, this same query will be covered by index ism at the very start of this article.

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:

  • Usage of covered array indexes for larger arrays
  • Brings more efficiency and performance to queries using covered array indexes

Indexing Document Meta Information

Let’s take a turn here and discuss the new meta information that can be indexed. Previously indexes could be created on the meta().id property, but now both the meta().cas and meta().expiration properties are supported.

So how do we create an index that uses the meta properties as keys? It isn’t any different from what you already know. Take the following covering indexes for example:

CREATE INDEX idx_cas
ON `default` ( META().cas, META().expiration )

CREATE INDEX idx_exp
ON `default` ( META().expiration )


Now, if I wanted to use the idx_cas and idx_exp indexes in a N1QL query, I could do something like the following:

SELECT META().id, META().cas
FROM default
WHERE META().cas > 1489531586248179712;

SELECT META().id, META().expiration
FROM default
WHERE META().expiration > NOW_MILLIS();


So why would being able to index these properties be a benefit? Well, what if you wanted to do a query for all documents that had expired today or all documents that were modified today?

For more information on the CAS and expiration properties, visit here. For more information on indexing or using N1QL with Couchbase, check out the Couchbase Developer Portal.

Data structure Database dev Build (game engine)

Published at DZone with permission of Nic Raboy, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • How To Use Terraform to Provision an AWS EC2 Instance
  • Public Cloud-to-Cloud Repatriation Trend
  • Choosing the Best Cloud Provider for Hosting DevOps Tools
  • How Do the Docker Client and Docker Servers Work?

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: