Perform Various N1QL Queries Without Indexes in Couchbase Server

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 to 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:

{
    "type": "person",
    "firstname": "Nic",
    "lastname": "Raboy",
    "social_media": [
        {
            "website": "https://www.thepolyglotdeveloper.com"
        }
    ]
}

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:

{
    "type": "person",
    "firstname": "Maria",
    "lastname": "Raboy",
    "social_media": [
        {
            "website": "https://www.mraboy.com"
        }
    ]
}

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:

SELECT * 
FROM example 
USE KEYS ["nraboy", "mraboy"];

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, Getting Multiple Documents by Key in a Single Operation Eith 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:

{
    "type": "company",
    "name": "Couchbase Inc",
    "address": {
      "city": "Mountain View",
      "state": "CA"
    }
}

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:

{
    "type": "person",
    "firstname": "Nic",
    "lastname": "Raboy",
    "social_media": [
        {
            "website": "https://www.thepolyglotdeveloper.com"
        }
    ],
    "company": "couchbase"
}

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:

SELECT
    p.firstname,
    p.lastname,
    (SELECT c.* FROM example c USE KEYS p.company)[0] AS company
FROM example p 
USE KEYS ["nraboy", "mraboy"];

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.