NoSQL with Couchbase: Querying with N1QL

N1QL, SQL for JSON

Even though the main purpose of document-based databases is key-value operations, Couchbase still supports more complex SQL style queries with N1QL (nickel). N1QL is a query language which is described as SQL for JSON.

Make sure to check out the other articles in the series as well:

  1. NoSQL with Couchbase: Setting everything up with Docker
  2. NoSQL with Couchbase: Getting started
  3. NoSQL with Couchbase: Working with JSON
  4. NoSQL with Couchbase: Querying with N1QL
  5. NoSQL with Couchbase: Creating Indexes that Scale

In my latest article, we checked how we can work with JSON in an effective way. In this article, we will still work with JSON but instead of getting JSON documents by key-value, we are going to retrieve them with N1QL.

Preparing our Person class

We start by adding a new field, company, to our Person class. We are going to implement a REST endpoint for retrieving a list of persons working at a company.

Checking and handling N1qlQueryResult

Next, I prefer to have some generic class that handles and processes the N1QL queries. A N1QL query returns a N1qlQueryResult which has a couple of interesting fields which are good to inspect. One of them is info which can store some metrics, but this info object doesn’t always exist, but if it does it’s good to at least log it on DEBUG level. Another interesting field is the errors() which returns a list of errors that occurred (if they occurred) in the request. We will notice the importance of this field later in the article. Last but not the least, it also, of course, contains the rows of results if the query went fine.

Our class QueryResultChecker has so far only one method, processQuery(DbOperationType, Supplier.

The supplier will be the query against the bucket which returns a N1qlQueryResult. If there are any errors in the query, we log it on WARN level.

Deserializing the result

The N1qlQueryResult will include N1qlQueryRows which has a value that is a JsonObject, so when querying by N1QL we do not get a JsonDocument as we got from the key-value operations. This means we have to write another deserialize method that takes a JsonObject. We also need to pass in the bucket name in the constructor in order to deserialize it into our Person class.

Writing the N1QL query

We are now ready to write the N1QL query statement in our PersonRepository class. We will use parameterized queries and Couchbase expressive domain-specific language (DSL).

This will translate into the following query.

Our full PersonRepository class looks like the following.

Because we use a parameterized statement, we also need to provide a JsonArray with the values that we want to insert, and they need to be in the right order.

The DbOperationType is simply just an enum that is used for the logging to be able to figure out which query any potential metrics and errors come from.

REST endpoint

Let’s try out our query by creating up the REST endpoint for querying which persons work at the provided company.

We start by inserting a person with a company by running a POST request to http://localhost:9000/person with body:

Then we run a GET request to http://localhost:9000/person?company=starbucks.

Hmm, we didn’t receive anything. Let’s check our logs.

Error during N1QL_QUERY_GET_PERSONS_BY_COMPANY: {“msg”:”No index available on keyspace thecuriousdev-demo that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”,”code”:4000}

Indexes

Well, at least our checking for errors seems to work well. The message explains very well what is missing. We are missing an index to support our query. Let’s do the easiest possible fix; adding a primary index. Open up the GUI for the Couchbase and go into the Query workbench and run the following statement:

A primary index is quite good to have in the early stages of development because it lets you run ad-hoc queries. However, it is not recommended for production or the later part of your development process when you are trying to optimize the performance of your application. A primary index is basically the equivalent to full table scans in the SQL world.

Running GET request once again now returns:

Final words

We have looked at how we can run N1QL queries through the Couchbase Java SDK. We have looked a bit at some good methods that you should inspect and a good way to make handling of N1QL queries more generic which is nice because your application will probably have a couple of those.

The code for this article can be found here.

In the next article, we will dive a bit deeper on indexes. We will remove our primary index and create a more appropriate index that will be more performant. We will also go through some best practices when it comes to indexes.

You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *