Running your first N1QL query
Learning about N1QL
We have many resources that you can use to learn about N1QL:
- The online interactive tutorial allows you to learn about N1QL without having Couchbase Server installed in your own environment. It's a self-contained tutorial that runs in a web browser and lets you modify the sample queries. The tutorial covers SELECT statements in detail, including examples of JOIN, NEST, GROUP BY, and other typical clauses.
- The N1QL query translator displays SQL statements and equivalent N1QL statements. When you compare the statements, you'll find that in most cases they are identical.
- The N1QL guide contains a section about N1QL basics and a N1QL language reference.
- The N1QL cheat sheet provides a concise summary of the basic syntax elements. Print it out and keep it on your desk where it'll be handy for quick reference.
- Live and recorded Webinars are presented by Couchbase engineers and product managers. In the webinars they discuss features and use cases of Couchbase Server, including N1QL. Here are some links to webinars devoted entirely to N1QL: Couchbase 103: Querying and Ad hoc Querying for NoSQL.
- Couchbase blogs include articles written by SDK developers.
- The Couchbase forum is a community resource where you can ask questions, find answers, and discuss N1QL with other developers and the Couchbase team.
Working with N1QL
After you have Couchbase Server installed and the query service enabled, you can start using N1QL. You can use N1QL in your applications or by running N1QL queries against your database through the interactive query shell, cbq.
To run the query shell from a node in your Couchbase installation:
- Open a command window.
- Change to the Couchbase tools directory:
$ cd /opt/couchbase/bin
- Enter the command to start the interactive query shell:
$ ./cbq -engine=http://localhost:8093
If you are connected to the machine that runs the Couchbase query service, the host name is localhost as shown in the command above. If you are connected to a node other than the one that hosts the Couchbase query service, you need to use the host name or IP address of the query service node rather than localhost.
- If you haven't yet indexed the bucket you want to query, the first thing you need to do is
create the primary index:
cbq> CREATE PRIMARY INDEX ON `bucket-name` USING GSI;
In the example command, `bucket-name` represents the name of the bucket you want to work with. Any identifier that has a hyphen character in the name must be enclosed in backtick (`) characters.
Trying out N1QL with a sample bucket
To try N1QL right away, you can use cbq to run queries against one of the sample buckets that we include with Couchbase Server. You need to know the host name of the machine that is running the query service. If you installed Couchbase Server on your local computer, the host name is localhost.
The following steps show how to run N1QL queries against the beer-sample bucket. If you aren't sure whether the beer-sample bucket is installed, you can check for it and install it by using the administrator console:
- Open the Couchbase administrator console and log in.
- Choose and verify whether the beer-sample bucket is installed.
- If the beer-sample bucket is not installed, follow the on-screen instructions to install it.
Now you can start running queries against the beer-sample bucket:
- Run the following command to create the primary index:
Because the bucket name contains a hyphen character, you need to enclose the name of the bucket within backtick characters. In fact, for all identifiers that contain the hyphen character, you need to enclose the string in backtick characters.
cbq> CREATE PRIMARY INDEX ON `beer-sample` USING GSI;
- Now that you have the bucket indexed, you can run some queries to explore the data
in the beer-sample bucket. Here's a few sample queries to get you
started.The following query returns the different values used for the type field:
cbq> SELECT DISTINCT type FROM `beer-sample`;
Each document in the bucket contains a type field that indicates the kind of data the document contains. The beer-sample bucket contains two kinds of documents—documents that describe breweries (the value for type is brewery) and documents that describe beers (the value for type is beer).
The following query returns one brewery document and lists all the fields it contains:
cbq> SELECT * FROM `beer-sample` WHERE type="brewery" LIMIT 1;
The beer-sample bucket contains over 7000 documents, so the queries shown here contain a LIMIT clause to minimize the number of rows returned.The following query returns all fields in one beer document. The IS NOT MISSING clause on the brewery_id field tells N1QL to only return documents that have a brewery_id field.
cbq> SELECT * FROM `beer-sample` WHERE brewery_id IS NOT MISSING AND type="beer" LIMIT 1;The following query returns the brewery_id and name fields from 5 beer documents:
cbq> SELECT brewery_id, name FROM `beer-sample` WHERE brewery_id IS NOT MISSING AND type="beer" LIMIT 5;
The following query returns 5 beer documents, but includes only the brewery_id field for each document. It orders them alphabetically by the brewery-id field and does not include any documents that do not have a brewery_id field.
cbq> SELECT DISTINCT brewery_id FROM `beer-sample` WHERE brewery_id IS NOT MISSING ORDER BY brewery_id LIMIT 5;
- When you are finished, type control-D to exit cbq and return to the command prompt.