Selecting Data

    +
    How to use a N1QL selection query to read data from a data source and return results.

    Introduction

    The Query Service enables you to create, read, update, and delete data by means of N1QL, the Couchbase Server query language. To read data from a data source using N1QL, you must use a selection query; that is, a query using the SELECT statement.

    Before You Begin

    If you want to try out the examples in this section, follow the instructions given in Do a Quick Install to install Couchbase Server, configure a cluster, and load a sample dataset.

    Query Tools

    Read the following for further information about the tools available for editing and executing queries:

    Selecting

    A selection query enables you to read information from a data source, perform operations on the data, and return the results.

    To specify what the query should return, use the SELECT clause.

    • N1QL

    • .NET

    • Java

    • Node.js

    • Python

    The following example uses the SELECT clause by itself to evaluate an expression.

    Query
    SELECT "Hello world" AS greeting;
    Result
    [
      {
        "greeting": "Hello world"
      }
    ]

    The following example uses the Cluster.QueryAsync<T> method to execute the query. The result includes each row found.

    // Call the QueryAsync() function on the cluster object and store the result.
    var queryResult = await cluster.QueryAsync<dynamic>("select \"Hello World\" as greeting");
    
    // Iterate over the rows to access result data and print to the terminal.
    await foreach (var row in queryResult) {
        Console.WriteLine(row);
    }

    Click the GitHub button to view this code in context.

    The following example uses the query() method to execute the query. The result object includes each row found.

    // Call the query() method on the cluster object and store the result.
    QueryResult result = cluster.query("select \"Hello World\" as greeting");
    
    // Return the result rows with the rowsAsObject() method and print to the terminal.
    System.out.println(result.rowsAsObject());

    Click the GitHub button to view this code in context.

    The following example uses the query() function to execute a query. The result object includes an array of rows found.

    // Call the query() function on the cluster object and store the result.
    const result = await cluster.query('SELECT "Hello World" as greeting')
    
    // Iterate over the rows to access result data and print to the terminal.
    result.rows.forEach((row) => {
      console.log(row)
    })

    Click the GitHub button to view this code in context.

    The following example uses the query() function to execute a query. The result object includes an array of rows found.

    # Call the query() function on the cluster object and store the result.
    result = cluster.query("SELECT \"Hello World\" as greeting")
    
    # Iterate over the rows to access result data and print to the terminal.
    for row in result.rows():
        print(row)

    Click the GitHub button to view this code in context.

    For more information and examples, refer to SELECT Clause.

    Specifying a Data Source

    To specify the data source for a query, use the FROM clause. For example, to get data from a collection, specify the path to that collection.

    When you specify a FROM clause, you can use the SELECT clause to specify the fields that you want to return from that data source. The set of fields returned by the query is known as the projection.

    Before you can query a data source, there must be an index on that data source. The sample dataset already contains all the indexes you need to run the example queries in this guide.

    The following query gets the name and city of every airport.

    Query
    SELECT airportname, city
    FROM `travel-sample`.inventory.airport;
    Result
    [
      {
        "airportname": "Calais Dunkerque",
        "city": "Calais"
      },
      {
        "airportname": "Peronne St Quentin",
        "city": "Peronne"
      },
    // ...
    ]

    For more information and examples, refer to FROM Clause.

    Filtering

    To filter the results of the query, use the WHERE clause to specify a comparison expression. Only records that satisfy the comparison expression are returned.

    For example, the following query finds the name and city of every airport in the Anchorage timezone whose altitude is greater than or equal to 2100.

    Query
    SELECT t.airportname, t.city
    FROM   `travel-sample`.inventory.airport t
    WHERE  tz = "America/Anchorage"
           AND geo.alt >= 2100;
    Result
    [
      {
            "airportname": "Anaktuvuk Pass Airport",
            "city": "Anaktuvuk Pass",
      }
    ]

    For more information and examples, refer to WHERE Clause.

    Limiting Results

    To limit the number of documents returned by a query, use the LIMIT clause.

    For example, the following query finds only 2 hotels with an empty room.

    Query
    SELECT name, address, city, country, url
    FROM `travel-sample`.inventory.hotel
    WHERE vacancy = true
    LIMIT 2;
    Result
    [
      {
        "address": "Capstone Road, ME7 3JE",
        "city": "Medway",
        "country": "United Kingdom",
        "name": "Medway Youth Hostel",
        "url": "http://www.yha.org.uk"
      },
      {
        "address": "6 rue aux Juifs",
        "city": "Giverny",
        "country": "France",
        "name": "The Robins",
        "url": "http://givernyguesthouse.com/robin.htm"
      }
    ]

    For more information and examples, refer to LIMIT Clause.

    Ordering Results

    To sort the documents in the resultset by one or more fields, use the ORDER BY clause.

    For example, the following query lists cities in descending order and then landmarks in ascending order.

    Query
    SELECT city, name
    FROM `travel-sample`.inventory.landmark
    ORDER BY city DESC, name ASC
    LIMIT 5;
    Results:
    [
      {
        "city": "Évreux",
        "name": "Cafe des Arts"
      },
      {
        "city": "Épinal",
        "name": "Marché Couvert (covered market)"
      },
      {
        "city": "Épinal",
        "name": "Musée de l'Image/Imagerie d'Épinal"
      },
      {
        "city": "Yosemite Valley",
        "name": "Lower Yosemite Fall"
      },
      {
        "city": "Yosemite Valley",
        "name": "Mirror Lake/Meadow"
      }
    ]

    For more information and examples, refer to ORDER BY Clause.

    In-depth explanation:

    Reference:

    Tutorials:

    Querying with SDKs: