As you probably already know, you’re able to query Couchbase NoSQL documents using a SQL dialect called N1QL.  This is made possible through indexes that you create on documents in your Couchbase Buckets.  However, what if I told you that not every N1QL query requires an index to first exist?  After talking with my colleague, Justin Michaels, he showed me an awesome trick to perform bulk operations in N1QL without indexes.  This was news to me because I always thought you needed at least one index to exist, but hey, you learn something new every day.

We’re going to see how to run a few N1QL queries on a Couchbase Bucket that has no indexes and that includes no primary index.

Before we jump into some sample scenarios, you might be wondering how it is possible run queries without an index.  This is actually possible by making use of the USE KEYS operator to hone in on specific documents by their key that exists in the meta information.

Take the following document for example:

Above we have a simple document that represents a particular person.  Let’s say the above document has nraboy as the id value.  To make things interesting, let’s create another document.

Assume the following has mraboy as the id value:

So if we wanted to query either of these two documents with the USE KEYS operator in N1QL, we could compose a query that looks like the following:

If you look at the EXPLAIN of the above query you’ll notice that no index was used in the query.  The above type of query would be useful if you knew the keys that you wanted to obtain and wanted incredibly fast performance similar to how it was done in a previous article I wrote titled, Getting Multiple Documents by Key in a Single Operation with Node.js.

Let’s make things a bit more complicated.  What if we wanted to query with a relationship on one or more of the document properties?

Let’s create another document with couchbase as the document id:

The above document represents a company.  As you probably guessed, we’re going to query for the company information of each person.  To make this possible, let’s change the nraboy document to look like the following:

Notice we’ve added a property with the key to our other document.  We won’t add any company information to the mraboy document.

Take the following query that has a multiple document relationship, but no indexes created:

Notice that the above query has a subquery that also uses the USE KEYS operator.  Not bad right?  Try using other operators like UNNEST to flatten the array data found in the social_media property.

Conclusion

You just saw how to write N1QL queries in Couchbase that use no index.  By using the USE KEYS operator we can do bulk operations based on key, like I demonstrated in the articles, Getting Multiple Documents by Key in a Single Operation with Node.js and Using Golang to get Multiple Couchbase Documents by Key in a Single Operation.  A huge thanks to Justin Michaels from Couchbase for helping me with this.

To learn more about N1QL and Couchbase, check out the Couchbase Developer Portal for more information.

Posted by Nic Raboy, Developer Advocate, Couchbase

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in Java, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Apache Cordova. Nic writes about his development experiences related to making web and mobile development easier to understand.

Leave a reply