Running your first N1QL query

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

Learning 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.

Making N1QL queries

After you have Couchbase Server installed, 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, 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:

  1. Open a command window on a Couchbase Server node that has the query service enabled.
  2. Enter the command to start the interactive query shell:
    # On Linux systems:
    $ /opt/couchbase/bin/cbq
    # On OS X systems:
    $ /Applications/Couchbase\
  3. 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.

    You need to end each command with a semicolon, and then press enter to execute the query.

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 is included 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:

  1. Open the Couchbase administrator console and log in.
  2. Choose Settings > Sample Buckets and verify whether the beer-sample bucket is installed.
  3. If the beer-sample bucket is not installed, follow the on-screen instructions to install it.

Now you can use cbq to run some queries against the beer-sample bucket:

  1. Start cbq.

    You are now at the cbq command prompt and can start running N1QL queries. Remember to end each query with a semicolon.

  2. Run the following command to create the primary index:
    cbq> CREATE PRIMARY INDEX ON `beer-sample` USING GSI;
    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.
  3. 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;
  4. When you are finished, type control-D to exit cbq and return to the command prompt.