A newer version of this documentation is available.

View Latest
February 16, 2025
+ 12

How to use a SQL++ selection query to read data from a data source and return results.
This guide is for Couchbase Server.

Introduction

The Query Service enables you to create, read, update, and delete data by means of SQL++, the Couchbase Server query language. To read data from a data source using SQL++, 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.

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

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

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.

Setting the Query Context

The query context enables you to specify a bucket and scope to resolve partial keyspace references within your queries. When the query context is set, you can specify the data source in your queries using the collection name only. This enhances the portability of your queries.

The query context is only used to resolve partial keyspace references. When a query specifies a data source using the full path to a keyspace, the query context is not used to resolve that keyspace.

To set the query context:

  1. Using the context controls at the top right of the Query Editor, open the bucket drop-down menu and select the required bucket.

    When a bucket is selected, a scope drop-down menu is displayed to the right.

  2. Open the scope drop-down menu and select the required scope.

The query context menu with `travel-sample.inventory` selected

Some legacy queries contain keyspace references consisting of the bucket name only, referring to the default collection in the default scope. To specify the data source using the bucket name only, you must unset the query context.

To unset the query context, using the context controls at the top right of the Query Editor, open the bucket drop-down menu and select unset.

The scope drop-down menu disappears.

The context controls with the query context unset

For more information and examples, refer to Query Context.

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.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query
SELECT t.airportname, t.city
FROM   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.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query
SELECT name, address, city, country, url
FROM 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.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query
SELECT city, name
FROM 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: