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.

3 Comments

  1. Hi, I need to perform a query on multiple documents for data buckets without index.
    I understood that I can use the operator USE KEYS with a list of id values
    e.g USE KEYS [‘a’, ‘b’]
    In my use case I have a list of several document id.
    Is it possible to write a query like USE KEYS [‘*all document id’] instead of specifying all the id values in the array USE KEYS [‘a’, ‘b’, ….., ‘n’] ?
    Otherwise could you suggest another way to write this kind of query.
    Thanks

    1. If you don’t want to use an index, you’d have to specify all the keys. The index keeps track of documents, allowing you to do such queries on all document ids. No index means there is nothing keeping track of the documents, leaving you with key-value only operations.

      Maybe keep a master document that is just an array of document ids? Then when you need to query, lookup the document and use the keys of the array in the USE KEYS statement?

  2. ok, it could be a solution.
    Thanks for your reply

Leave a reply