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:

Query

SELECT name FROM `beer-sample` WHERE  brewery_id ="mishawaka_brewing";

Result

{
    "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:
1. Open the Couchbase Web Console and log in.
2. Choose Settings Sample Buckets and verify whether the beer-sample bucket is installed.
3. 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 the beer-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. 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 return only 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.

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.

Running your first N1QL query

Making N1QL queries

Trying out N1QL with the beer-sample bucket

Learning more about N1QL

Trying out N1QL with the `beer-sample` bucket