Run Your First N1QL Query

Now that you have a basic understanding of buckets and documents, you can try querying them using N1QL (pronounced "nickel"), 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. You can run N1QL queries from the command line, using the cbq tool; or from the Query Workbench in the Couchbase Server 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 that is associated with the airline Excel Airways:

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

Note that for all identifiers (bucket names) that contain a hyphen character, you need to enclose the name with backtick (`) characters.

The results:

{
    "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.

Try the Interactive Query Shell

To run the interactive query shell, cbq, open a console window on your computer and enter the following:

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

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

cd /opt/couchbase/bin
./cbq -u Administrator -p password -engine=http://127.0.0.1:8091/

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

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

The results:

{
    "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 results thus contain 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 also search on a type. For example, the following query returns a maximum of one airport document, and lists all of the fields that it contains:

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

The results:

{
    "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;

The results:

{
    "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;

The results:

{
    "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,
        "sortCount": 3
    }
}

Try the Query Workbench

The Couchbase Server Web Console includes the Query Workbench, an interactive tool that lets you compose and execute N1QL queries. To use the Query Workbench, log into the Couchbase Server Web Console, and then click Query:

queryWorkbench

The Query Workbench has three principal areas:

  • Query Editor: Where you will type your N1QL query

  • Bucket Insights: Provides information on the buckets that are currently maintained by your system. Right now, it shows that just one exists; the bucket travel-sample.

  • Query Results: 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.

Use the Query Workbench to enter the following N1QL query:

SELECT name FROM `travel-sample` WHERE callsign = "MILE-AIR";
firstQuery

To execute your query, click Execute.

The results now appear in the Query Results panel:

queryResultsJSON

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

Next

The final step in the Getting Started sequence, Choose Your Next Steps, offers suggestions for learning more about Couchbase and using it for production use-cases.

Other Destinations

  • N1QL from the SDK: Explains how to execute N1QL queries programmatically using the official Couchbase SDKs.

  • N1QL Query Language Tutorial: Provides interactive web modules where you can learn about N1QL without having Couchbase Server installed in your own environment. The modules are self-contained and let you modify and run sample queries. The tutorial covers SELECT statements in detail, including examples of JOIN, NEST, GROUP BY, and other typical clauses.

  • N1QL Cheat Sheet: Provides a concise summary of the basic syntax elements of N1QL. Print it out and keep it on your desk where it’ll be handy for quick reference.

  • N1QL Language Reference: Describes the N1QL language structure, including syntax and usage.

  • Couchbase Webinars: Live and recorded presentations by Couchbase engineers and product managers that highlight features and use-cases of Couchbase Server, including N1QL.

  • Couchbase Blog: Regularly-posted technical articles and announcements written by Couchbase employees, including SDK developers.

  • Couchbase Forum: A community resource where you can ask questions, find answers, and discuss N1QL with other developers and the Couchbase team.