Selecting Data
- how-to
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.
SELECT "Hello world" AS greeting;[
{
"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 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 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 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.
SELECT airportname, city
FROM `travel-sample`.inventory.airport;[
{
"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.
SELECT t.airportname, t.city
FROM `travel-sample`.inventory.airport t
WHERE tz = "America/Anchorage"
AND geo.alt >= 2100;[
{
"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.
SELECT name, address, city, country, url
FROM `travel-sample`.inventory.hotel
WHERE vacancy = true
LIMIT 2;[
{
"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.
SELECT city, name
FROM `travel-sample`.inventory.landmark
ORDER BY city DESC, name ASC
LIMIT 5;[
{
"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.