The 5 Most Common Forum’s Questions

How to do “something” in N1QL?

First of all, if you are not familiar with N1QL I highly recommend you to spend a few minutes in our free N1QL training here,  or just play with it here.

Second, as it is a broad question, let’s go through some common scenarios:

Select the id of a document and all its attributes:

How to write a JOIN:

Let’s query which companies fly from San Francisco airport (SFO) to anywhere in the world using the travel sample:

The JOIN clause looks like a standard SQL JOIN, the only difference here is the ON KEYS keyword, to read more about it check this article explaining visually N1QL JOINs. Couchbase 5.5 will also add support to ANSI JOINs

How to select items of an array:

Given documents like:

If we want to select all children who are more than 10 years old, we could use the UNNEST keyword:

Why my query is slow?

Probably your query is not hitting any indexes. You can check that by running your query with the explain keyword, as follows:

As you can see in the image above, the query is hitting the PrimaryScan which means that it is using the primary index. Creating a secondary index for it will potentially solve your problem:

Executing the same query again will output something like:

If your query is already hitting an index but still has a poor performance, you might want to add a more optimized index (like in this example). If you are not familiar with how to create an index, check out this blog post

How to paginate results in N1QL?

You can use LIMIT and OFFSET:

Check out this tutorial to read more about it. Additionally, if you are using Spring Data you can add a Pageable object to the end of your method definition:

And then, in your Service you can use the PageRequest object:

My query has missing results/wrong results

By default, Couchbase supports read-after-writes whenever you get a document by its key, but your indexes and views are updated asynchronously via Data Change Protocol (DCP). So, if you are executing a query right after a write, it might be executed before the views/indexes had a chance to be updated.

Couchbase is all about speed, and no one has time to wait until all indexes and views are updated to send the response back to the client that a write has been executed successfully.

But there are few scenarios where strong consistency between writes and your queries are actually needed, for those cases you can specify via SDK that you actually want to wait until the index/view you are using is updated:

To read more about scan consistency, please refer to the official documentation.

In my personal experience, I only scenario where I do need consistency between writes and queries is during the integration tests phase, which is when you actually insert data and query it right after.

How to Creating/use Array Indexes.

This is an interesting topic, as array indexing might speed up significantly your performance. So, let’s say we have the following document structure:

Now, if we need to query hotel reviews, we could do something like:

So, the simplest index for the reviews array will look like the following:

And then, when we execute the query, voilà:

It is using the recently created index.

For more examples, check the official documentation or read this excellent article about how to optimize array indexes.