Running your first N1QL query
N1QL (pronounced "nickel") is the Couchbase Server query language.
N1QL embraces the JSON document model and uses SQL-like syntax. In N1QL, you operate on JSON documents, and the result of your operation is another JSON document.
A basic N1QL query has these parts:
-
SELECT
—the fields of each document to return -
FROM
—the data bucket to look in -
WHERE
—conditions the document must satisfy
Here’s an example of a basic N1QL query and the JSON document it returns. The following query asks for the names of beers produced by the Mishawaka Brewing Company:
SELECT name FROM `beer-sample` WHERE brewery_id ="mishawaka_brewing";
{
"requestID": "fb844e2b-0f9a-422c-bda5-1f81cebd7a72",
"signature": {
"name": "json"
},
"results": [
{
"name": "Wall Street Wheat Ale"
},
{
"name": "Lake Effect Pale Ale"
},
{
"name": "Raspberry Wheat Ale"
},
{
"name": "Kolsch"
},
{
"name": "Four Horsemen Ale"
},
{
"name": "INDIAna Pale Ale"
}
],
"status": "success",
"metrics": {
"elapsedTime": "474.080629ms",
"executionTime": "474.045631ms",
"resultCount": 6,
"resultSize": 303
}
}
Making N1QL queries
After you install Couchbase Server, you can start using N1QL right away.
You can make N1QL requests in your applications or run N1QL queries against your database through the interactive query shell called cbq
.
To learn how to send N1QL queries from an application, read the N1QL topic in the SDK for the language you are using.
To run cbq
from a node in your Couchbase installation:
-
Open a command window on a Couchbase Server node that has the query service enabled.
-
Enter the command to start the interactive query shell:
-
On Linux systems: $
/opt/couchbase/bin/cbq
-
On OS X systems: $
/Applications/Couchbase\ Server.app/Contents/Resources/couchbase-core/bin/cbq
-
On Windows systems: $
C:\Program Files\Couchbase\Server\bin\cbq.exe
-
-
To run queries, create a primary index on a bucket, such as the
default
bucket installed by default. The primary index contains a list of every document within the database, with the document ID as the key.cbq> CREATE PRIMARY INDEX ON default USING GSI;
Trying out N1QL with the beer-sample
bucket
Try N1QL and use cbq
to run queries against the beer-sample
bucket you installed earlier.
Use the hostname of the machine running the query service: if you installed Couchbase Server on your local machine, the host name is localhost
.
-
Verify that the
beer-sample
bucket is installed:-
Open the Couchbase Web Console and log in.
-
Choose
and verify whether thebeer-sample
bucket is installed. -
If it is not available, follow the on-screen instructions to install it.
-
-
Start
cbq
on your system (Linux, Mac OSX, or Windows) to to run some queries against thebeer-sample
bucket.- On Linux systems
-
$ /opt/couchbase/bin/cbq
- On OS X systems
-
$ /Applications/Couchbase\ Server.app/Contents/Resources/couchbase-core/bin/cbq
- On Windows systems
-
$ C:\Program Files\Couchbase\Server\bin\cbq.exe
You are now at the
cbq
command prompt and can start running N1QL queries. Remember to end each query with a semicolon. -
Run the following command to create the primary index:
cbq> CREATE PRIMARY INDEX ON `beer-sample` USING GSI;
Remember that for all identifiers (bucket names) that contain a hyphen character you need to enclose the name with backtick (`) characters.
-
Now that your bucket is indexed, you can run some queries to explore the data it contains. Here are 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. Thebeer-sample
bucket contains two kinds of documents—documents that describe breweries (the value fortype
isbrewery
) and documents that describe beers (the value fortype
isbeer
).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 aLIMIT
clause to minimize the number of rows returned.The following query returns all fields in one
beer
document. TheIS NOT MISSING
clause on thebrewery_id
field tells N1QL to return only documents that have abrewery_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
andname
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 thebrewery-id
field and does not include any documents that do not have abrewery_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.
Learning more about N1QL
In addition to following this brief tutorial, you can learn more about N1QL by looking at these in-depth resources:
-
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 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.
-
The N1QL reference guide contains details about N1QL syntax and usage.
-
Live and recorded Webinars presented by Couchbase engineers and product managers highlight 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 Couchbase 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.