Read Data and Return Results

Capella Operational

how-to

How to use a SQL++ 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 SQL++, the Couchbase Capella 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 Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy 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.

SQL++
.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.

Unresolved include directive in modules/guides/pages/select.adoc - include::dotnet-sdk:hello-world:example$StartUsing.cs[]

Click the View button to see 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 scope object and store the result.
Scope inventoryScope = bucket.scope("inventory");
QueryResult result = inventoryScope.query("SELECT * FROM airline WHERE id = 10;");

// Return the result rows with the rowsAsObject() method and print to the terminal.
System.out.println(result.rowsAsObject());

Click the View button to see 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 View button to see 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 View button to see 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 in a FROM clause.

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.

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

Query

SELECT airportname, city
FROM `travel-sample`.inventory.airport;

There are backticks around the travel-sample dataset because its name contains a hyphen.

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.

Query Tab
CBQ Shell

To set the query context:

Using the Query Context controls in the Query tab, open the Bucket drop-down menu and select the required bucket.
Open the Scope drop-down menu and select the required scope.

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

To set the query context, use the \SET command with the query_context parameter.

For example, the following command sets the query context to travel-sample.inventory.

\SET -query_context travel-sample.inventory;

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.

Query Tab
CBQ Shell

To unset the query context, using the Query Context controls in the Query tab, open the Bucket drop-down menu and choose Select a bucket.

The query context controls with the query context unset

To unset the query context, use \UNSET command with the query_context parameter.

For example, the following command unsets the query context.

\UNSET -query_context;

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:

SELECT

Reference:

SELECT Syntax

Tutorials:

SQL++ Query Language Tutorial

Querying with SDKs:

C | .NET | Go | Java | Node.js | PHP | Python | Ruby | Scala

Read Data and Return Results

Introduction

Before You Begin

Query Tools

Selecting

Specifying a Data Source

Setting the Query Context

Filtering

Limiting Results

Ordering Results

Related Links