Chapter 7. Understanding SQL-Like Queries – N1QL

In the previous chapter, you learned how to fetch documents using the MapReduce functionalities of views. Therein, you need to create a view before querying for any documents when not using a document ID. Couchbase has developed an efficient way of retrieving documents using a SQL-like syntax, called N1QL. N1QL is simpler and easier to understand. The syntax is more or less like that of SQL, so developers who are from RDBMS's SQL background will find themselves very much at home.

This method makes it easier to retrieve information from Couchbase. The main difference between SQL and N1QL (pronounced as Nickel) is that N1QL provides a way to query the JSON based document-oriented database, Couchbase. N1QL offers a data definition language (DDL), a data manipulation language (DML), and queries to extract information in Couchbase.

In this chapter, we will see how to set up N1QL to fetch data from a bucket using SQL-like syntax, and then discuss...

The N1QL overview

So far, you have learned how to fetch documents in two ways: using document ID and views. If you are reading this book sequentially from the first chapter, you will agree with me. Otherwise, I recommend that you go through Chapter 6, Retrieving Documents without Keys Using Views, in order to understand this chapter better.

The third way of retrieving documents is by using N1QL. Personally, I feel that it is a great move by Couchbase to provide SQL-like syntax, since most engineers and IT professionals are quite familiar with SQL, which is usually part of their formal education. It brings confidence in them and also provides ease of using Couchbase in their applications. Moreover, it provides most database operational activities related to development.

N1QL can be used to:

• Store documents, that is, the INSERT command

• Fetch documents, that is, the SELECT command

Prior to the advent of N1QL, developers need to perform key-based operations, which were quite complex when it came...

Operation types

With the release of DP4, N1QL is a full-fledged SQL-like language meant to fetch and perform various operations on a JSON based document stored in the bucket. Since we are working on N1QL DP3, we will only discuss the features provided by it, as follows:

Category	Description
Select	This provides features to extract the desired attributes from documents present in the bucket
Filter	You can filter documents according to the conditions of your application logic
Aggregate	You can perform aggregation by grouping attributes specified in the aggregate clause
Having	You can filter documents on the aggregate value after applying aggregation functions
Order	You can order resultset documents
Skip	You can perform skipping of records while fetching documents
Join	This can join multiple documents

You will be executing all these types of queries from the Couchbase Query Engine CLI or from client APIs. We will be showing you all this queries' examples using CLI...

Understanding N1QL syntax

Most N1QL queries will be in the following format:

SELECT [DISTINCT] <expression>
FROM <data source>
WHERE <expression>
GROUP BY <expression>
ORDER BY <expression>
LIMIT <number>
OFFSET <number>

The preceding statement is very generic. It tells you the comprehensive options provided by N1QL in one statement. Let me break it down into parts so that it can be understood easily.

I will be explaining these N1QL query syntax based on documents stored in the bucket, LearningCouchbase, which we created earlier. If you remember correctly, we have already entered some documents in the LearningCouchbase bucket as well. We will execute the N1QL queries only on those documents.

If we want to fetch all the users' documents in the LearningCouchbase bucket, but want to display only some information or attributes, that is, the name and the book attribute of the documents, it can be done as follows:

SELECT name,book
FROM LearningCouchbase...

Indexing properties

Querying an unindexed property causes the server to scan the entire bucket and check that property on every document. You can create indexes with the following syntax:

CREATE INDEX Index_name
ON LearningCouchbase(name)

Here, Index_name is the name of the index you want to create, LearningCouchbase is the name of the bucket, and name is the attribute of documents in which you want to create the index.

Note

One thing you need to remember is that you cannot execute any N1QL query until you create a primary index with the query shown next.

CREATE PRIMARY INDEX ON LearningCouchbase

Views

When you create indexes on a bucket; internally, Couchbase creates views on your behalf. As shown in the preceding screenshot, you can find views in the view editor after you create indexes.

Explaining a query

Sometimes, we like to know how queries will be executed in the Couchbase cluster. It helps us to tune it while in the development phase. You just need to append the EXPLAIN keyword before...

Using the N1QL API

Let's understand the Java APIs to use N1QL queries in our Java application. In order to demonstrate it, let's create another Maven project with the following details:

ModelVersion: 4.0.0
GroupId: com.ht
ArtifactId: LearningCouchbaseN1QL

You need to ensure that the following dependency is included in your pom.xml project:

<dependency>
<groupId>com.couchbase.client</groupId>
<artifactId>java-client</artifactId>
<version>2.0.0</version>
</dependency>

You also need to ensure that the version you use for this module is 2.0.0. At the time of writing this book, this is the only version compatible with the N1QL Developer Preview, which we have used in this book:

package com.ht.cql.view;

import com.couchbase.client.java.Bucket;
import com.couchbase.client.java.Cluster;
import com.couchbase.client.java.CouchbaseCluster;
import com.couchbase.client.java.query.QueryResult;
import com.couchbase.client.java.query.QueryRow;

/**
 * 
 ...

Summary

In this chapter, we discussed N1QL. You understood its architecture, its various operations, and the syntax provided by it. We also executed some queries on the Couchbase cluster using the CLI. At the end, we showed how to connect to the CBQ engine and execute N1QL queries using Java APIs.

In the next chapter, we will provide an overview of ElasticSearch, integrate it with Couchbase, and provide a text search mechanism using it.

The rest of the chapter is locked

Understanding N1QL syntax

Most N1QL queries will be in the following format:

SELECT [DISTINCT] <expression>
FROM <data source>
WHERE <expression>
GROUP BY <expression>
ORDER BY <expression>
LIMIT <number>
OFFSET <number>

The preceding statement is very generic. It tells you the comprehensive options provided by N1QL in one statement. Let me break it down into parts so that it can be understood easily.

I will be explaining these N1QL query syntax based on documents stored in the bucket, LearningCouchbase, which we created earlier. If you remember correctly, we have already entered some documents in the LearningCouchbase bucket as well. We will execute the N1QL queries only on those documents.

If we want to fetch all the users' documents in the LearningCouchbase bucket, but want to display only some information or attributes, that is, the name and the book attribute of the documents, it can be done as follows:

SELECT name,book
FROM LearningCouchbase...

Indexing properties

Querying an unindexed property causes the server to scan the entire bucket and check that property on every document. You can create indexes with the following syntax:

CREATE INDEX Index_name
ON LearningCouchbase(name)

Here, Index_name is the name of the index you want to create, LearningCouchbase is the name of the bucket, and name is the attribute of documents in which you want to create the index.

Note

One thing you need to remember is that you cannot execute any N1QL query until you create a primary index with the query shown next.

CREATE PRIMARY INDEX ON LearningCouchbase

Views

When you create indexes on a bucket; internally, Couchbase creates views on your behalf. As shown in the preceding screenshot, you can find views in the view editor after you create indexes.

Explaining a query

Sometimes, we like to know how queries will be executed in the Couchbase cluster. It helps us to tune it while in the development phase. You just need to append the EXPLAIN keyword before...

Using the N1QL API

Let's understand the Java APIs to use N1QL queries in our Java application. In order to demonstrate it, let's create another Maven project with the following details:

ModelVersion: 4.0.0
GroupId: com.ht
ArtifactId: LearningCouchbaseN1QL

You need to ensure that the following dependency is included in your pom.xml project:

<dependency>
<groupId>com.couchbase.client</groupId>
<artifactId>java-client</artifactId>
<version>2.0.0</version>
</dependency>

You also need to ensure that the version you use for this module is 2.0.0. At the time of writing this book, this is the only version compatible with the N1QL Developer Preview, which we have used in this book:

package com.ht.cql.view;

import com.couchbase.client.java.Bucket;
import com.couchbase.client.java.Cluster;
import com.couchbase.client.java.CouchbaseCluster;
import com.couchbase.client.java.query.QueryResult;
import com.couchbase.client.java.query.QueryRow;

/**
 * 
 ...

Summary

In this chapter, we discussed N1QL. You understood its architecture, its various operations, and the syntax provided by it. We also executed some queries on the Couchbase cluster using the CLI. At the end, we showed how to connect to the CBQ engine and execute N1QL queries using Java APIs.

In the next chapter, we will provide an overview of ElasticSearch, integrate it with Couchbase, and provide a text search mechanism using it.