Run Your First N1QL Query

Run Your First N1QL Query

N1QL (pronounced "nickel") is the Couchbase Server query language.

About N1QL

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. N1QL queries can be run from the command line, using the cbq tool; or by means of the Query Workbench, provided by the Couchbase Web Console.

A basic N1QL query has the following parts:

  • SELECT — The fields of each document to return.
  • FROM — The data bucket in which to look.
  • WHERE — The conditions that the document must satisfy.

Here's an example of a basic N1QL query and the JSON document it returns. The query asks for the country associated with the airline Excel Airways — note that for all identifiers (bucket names) that contain a hyphen character, you need to enclose the name with backtick (`) characters:

SELECT country FROM `travel-sample` WHERE name = "Excel Airways";

The result is as follows:

{
    "requestID": "9e1cd084-f45e-4059-9e7a-edec30f60dd2",
    "signature": {
        "country": "json"
	},
    "results": [
        {
            "country": "United Kingdom"
        }
    ],
    "status": "success",
	"metrics": {
        "elapsedTime": "7.42097249s",
        "executionTime": "7.420925841s",
        "resultCount": 1,
        "resultSize": 51
    }
}

The country is thus specified as United Kingdom.

Making N1QL queries

After you install Couchbase Server, you can start using N1QL right away; either with the interactive query shell, cbq; or with the Query Workbench, which is provided by the Couchbase Web Console.

Try the Interactive Query Shell

To run the interactive query shell, cbq, bring up a console window on your machine, and enter the following against the prompt:

$ bash -c "clear && docker exec -it db sh"

Then, navigate to the Couchbase bin directory, and start cbq:

# cd /opt/couchbase/bin
# ./cbq

This displays the cbq shell prompt, against which you can enter N1QL commands against your currently installed buckets. For example, the following query returns the different values used by documents in the travel-sample bucket for the callsign field, limiting the number of results to 5:

cbq> SELECT callsign FROM `travel-sample` LIMIT 5;

The result is as follows:

{
    "requestID": "ae6fcd5b-e7f0-4725-ae1d-a38678c13a3e",
    "signature": {
        "callsign": "json"
    },
    "results": [
        {
            "callsign": "MILE-AIR"
        },
        {
            "callsign": "TXW"
        },
        {
            "callsign": "atifly"
        },
        {
             "callsign": null
        },
        {
             "callsign": "LOCAIR"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "33.748019ms",
        "executionTime": "33.673901ms",
        "resultCount": 5,
        "resultSize": 215
    }
}

The result thus contains five callsign-values. A callsign is associated with an airline; and airline is one of the document-types that the travel-sample bucket contains. Others are airport and hotel. You can search on a type: for example, the following query returns a maximum of one airport document, and lists all the fields it contains.

cbq> SELECT * FROM `travel-sample` WHERE type="airport" LIMIT 1;

The query-result is as follows:

{
    "requestID": "c49a5885-9fde-40e3-871f-699f211078cc",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "travel-sample": {
                "airportname": "Calais Dunkerque",
                "city": "Calais",
                "country": "France",
                "faa": "CQF",
                "geo": {
                    "alt": 12,
                    "lat": 50.962097,
                    "lon": 1.954764
                },
                "icao": "LFAC",
                "id": 1254,
                "type": "airport",
                "tz": "Europe/Paris"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "16.272029ms",
        "executionTime": "16.216091ms",
        "resultCount": 1,
        "resultSize": 489
    }
}

The following query returns the names of (at a maximum) ten hotels that accept pets, in the city of Medway.

cbq> SELECT name FROM `travel-sample` WHERE type="hotel" AND city="Medway" and pets_ok=true LIMIT 10;
{
    "requestID": "b6dc75dd-4ed2-40de-83c8-9aebb3820ad8",
    "signature": {
        "name": "json"
    },
    "results": [
        {
            "name": "Medway Youth Hostel"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "45.380072ms",
        "executionTime": "45.326531ms",
        "resultCount": 1,
        "resultSize": 53
    }
}

The following query returns the name and phone fields for up to 10 documents for hotels in Manchester, where directions are not missing; and orders the results by name:

cbq> SELECT name,phone FROM `travel-sample` WHERE type="hotel" AND city="Manchester" and directions IS NOT MISSING ORDER BY name LIMIT 10;
{
    "requestID": "a3561cba-2377-4282-9c0f-68fc627950f6",
    "signature": {
        "name": "json",
        "phone": "json"
    },
    "results": [
    	{
            "name": "Hilton Chambers",
            "phone": "+44 161 236-4414"
    	},
        {
            "name": "Sachas Hotel",
            "phone": null
    	},
        {
            "name": "The Mitre Hotel",
            "phone": "+44 161 834-4128"
        },

    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "22.211069ms",
        "executionTime": "22.108582ms",
        "resultCount": 3,
        "resultSize": 253
    }
}

Try the Query Workbench

The Couchbase Web Console provides a Query Workbench, at which you can compose and execute N1QL queries. Left-click on the Query tab, located on the horizontal control-bar, near the top of the Couchbase Web Console:

This brings up the Query Workbench.

The workbench has three principal areas, which are:

  • An upper panel, which features the prompt Enter a query here: and this is indeed where you will type your N1QL query.

  • A Bucket Analysis panel, at the lower-left. This provides information on the buckets currently maintained by your system. Right now, it shows that just one exists; the bucket travel-sample.

  • A Results panel, at the lower-right. This shows query-results; and provides a number of options for their display. To start with, you will use the default option, which is selectable by the JSON button, and duly displays results in JSON-format.

You can now use the Query Workbench to enter a N1QL query. In the upper panel, enter the following:

To execute your query, left-click on the Execute button, at the upper-left:

Query-results now appear in the Results panel:

As you can see, a single document was found to match your specified criterion: which was the document whose name value is 40-Mile Air (which is, in fact, the document you took an initial look at, during the previous stage of the Getting Started sequence).

Next

You can execute N1QL queries programmatically, as well as manually. This is demonstrated by means of an off-the-shelf code example in the next section, Run Hello World!.

Other Destinations

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 Language Reference 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.