SELECT Clause
- Capella Operational
- reference
The SELECT
clause determines the result set.
Prerequisites
For you to select data from a document or keyspace, you must have the query_select
privilege on the document or keyspace.
For more details about user roles, see
Authorization.
Syntax
select-clause ::= 'SELECT' hint-comment? projection
![Syntax diagram](../_images/n1ql-language-reference/select-clause.png)
hint-comment | |
projection |
Optimizer Hints
In Couchbase Capella, you can supply hints to the optimizer within a specially-formatted hint comment. For further details, refer to Optimizer Hints.
Projection
projection ::= ( 'ALL' | 'DISTINCT' )? ( result-expr ( ',' result-expr )* |
( 'RAW' | 'ELEMENT' | 'VALUE' ) expr ( 'AS'? alias )? )
![Syntax diagram](../_images/n1ql-language-reference/projection.png)
The projection consists of an optional ALL
or DISTINCT
quantifier, followed by one of the following alternatives:
-
One or more result expressions, separated by commas.
-
A single raw expression, including a select expression and an optional alias.
ALL / DISTINCT
(Optional; default is ALL.)
SELECT ALL retrieves all of the data specified and will result in all of the specified columns, including all duplicates.
SELECT DISTINCT removes duplicate result objects from the query’s result set.
The DISTINCT clause is not blocking in nature, since it streams the input and produces the output in parallel, while consuming less memory. |
In general, SELECT ALL
results in more returned documents than SELECT DISTINCT
due to DISTINCT
's extra step of removing duplicates.
Since DISTINCT
is purely run in memory, it executes quickly, making the overhead of removing duplicates more noticeable as your recordset gets larger.
Refer to Example 4.
Result Expression
result-expr ::= ( path '.' )? '*' | expr ( 'AS'? alias )?
![Syntax diagram](../_images/n1ql-language-reference/result-expr.png)
path ::= identifier ( '[' expr ']' )* ( '.' identifier ( '[' expr ']' )* )*
![Syntax diagram](../_images/n1ql-language-reference/path.png)
The result expression may contain one of the following alternatives:
-
A star expression, preceded by an optional path.
-
A select expression, including an optional alias.
RAW / ELEMENT / VALUE
(Optional; RAW and ELEMENT and VALUE are synonyms.)
When you specify one or more result expressions in the query projection, each result is wrapped in an object, and an implicit or explicit alias is given for each result expression. This extra layer might not be desirable, since it requires extra output parsing.
SELECT RAW reduces the amount of data returned by eliminating the field attribute. The RAW qualifier specifies that the expression that follows should not be wrapped in an object, and the alias for that expression should be suppressed, as shown in Example 6 and Example 7.
The RAW qualifier only enables you to specify a single select expression. You cannot use the RAW qualifier with a star expression or with multiple select expressions.
Star Expression (*)
The star expression *
enables you to select all the fields from the source specified by the FROM clause.
The star expression may be preceded by a path, to select all the nested fields from within an array.
Omitting the keyspace name before a star expression adds the keyspace name to the result set; whereas if you include the keyspace name before a star expression, the keyspace name will not appear in the result set. Refer to Example 10. |
Select Expression
The select expression is any expression that evaluates to a field to be included in the query’s result set.
At its simplest, this may be the name of a field in the data source, such as id
, airline
, or stops
.
Refer to Example 1.
The select expression may include a path, to select a nested field from within an array, such as schedule[0].day
.
Refer to Example 2.
If no field name is specified, the select expression allows you to perform calculations, such as SELECT 10+20 AS Total;
or any other SQL++ expression.
For details with examples, see SQL++ Expressions.
AS Alias
alias ::= identifier
![Syntax diagram](../_images/n1ql-language-reference/alias.png)
A temporary name of a keyspace name or field name to make names more readable or unique. Refer to Example 3.
If you do not explicitly give a field an alias, it is given an implicit alias.
-
For a field, the implicit alias is the same as the name of the field in the input.
-
For a nested path, the implicit alias is defined as the last component in the path.
-
For any expression which does not refer to a field, the implicit alias is a dollar sign followed by a number, based on the position of the expression in the projection; for example,
$1
,$2
, and so on.
An implicit or explicit alias is returned in the result set, unless you suppress it using the RAW keyword.
Best Practices
When possible, explicitly list all fields you want in your result set instead of using a star expression *
to select all fields, since the *
requires an extra trip over your network — one to get the list of field names and one to select the fields.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
SELECT id, airline, stops FROM route LIMIT 1;
[
{
"airline": "AF",
"id": 10000,
"stops": 0
}
]
SELECT schedule[0].day FROM route;
[
{
"day": 0
}
]
SELECT schedule[0].day AS Weekday FROM route LIMIT 1;
[
{
"Weekday": 0
}
]
Note that the queries in this example may take some time to run.
Query 1
slightly slower |
Query 2
slightly faster |
When used on a field such as city
, which contains non-unique values, SELECT DISTINCT
reduces the recordset to a small fraction of its original size; and while removing so many of the documents takes time, projecting the remaining small fraction is actually slightly faster than the overhead of removing duplicates.
Query 3
much faster |
Query 4
much slower |
On the other extreme, when used on a field such as META().id
which contains only unique values, SELECT DISTINCT
does not reduce the recordset at all, and the overhead of looking for duplicates is wasted effort.
In this case, SELECT DISTINCT
takes about twice as long to execute as SELECT ALL
.
DISTINCT
operatorEXPLAIN SELECT DISTINCT city FROM landmark;
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan3",
"bucket": "travel-sample",
"index": "def_inventory_landmark_primary",
"index_projection": {
"primary_key": true
},
"keyspace": "landmark",
"namespace": "default",
"scope": "inventory",
"using": "gsi"
},
{
"#operator": "Fetch",
"bucket": "travel-sample",
"keyspace": "landmark",
"namespace": "default",
"scope": "inventory"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"distinct": true,
"result_terms": [
{
"expr": "(`landmark`.`city`)"
}
]
},
{
"#operator": "Distinct" (1)
}
]
}
},
{
"#operator": "Distinct" (1)
}
]
},
"text": "SELECT DISTINCT city FROM landmark;"
}
]
1 | Lines using the DISTINCT operator |
Query
|
Query
|
Results
|
Results
|
1 | Added implicit alias |
2 | No implicit alias |
Query
|
Query
|
Query
|
Results
|
Results
|
Results
|
airline
keyspaceSELECT * FROM airline LIMIT 1;
[
{
"airline": {
"callsign": "MILE-AIR",
"country": "United States",
"iata": "Q5",
"icao": "MLA",
"id": 10,
"name": "40-Mile Air",
"type": "airline"
}
}
]
landmark
keyspaceSELECT * FROM landmark LIMIT 1;
[
{
"landmark": {
"activity": "see",
"address": "Prince Arthur Road, ME4 4UG",
"alt": null,
"city": "Gillingham",
"content": "Adult - £6.99 for an Adult ticket that allows you to come back for further visits within a year (children's and concessionary tickets also available). Museum on military engineering and the history of the British Empire. A quite extensive collection that takes about half a day to see. Of most interest to fans of British and military history or civil engineering. The outside collection of tank mounted bridges etc can be seen for free. There is also an extensive series of themed special event weekends, admission to which is included in the cost of the annual ticket.",
"country": "United Kingdom",
"directions": null,
"email": null,
"geo": {
"accuracy": "RANGE_INTERPOLATED",
"lat": 51.39184,
"lon": 0.53616
},
"hours": "Tues - Fri 9.00am to 5.00pm, Sat - Sun 11.30am - 5.00pm",
"id": 10019,
"image": null,
"name": "Royal Engineers Museum",
"phone": "+44 1634 822839",
"price": null,
"state": null,
"title": "Gillingham (Kent)",
"tollfree": null,
"type": "landmark",
"url": "http://www.remuseum.org.uk"
}
}
]
SELECT * FROM hotel LIMIT 5;
[
{
"hotel": { (1)
"address": "Capstone Road, ME7 3JE",
"alias": null,
"checkin": null,
// ...
}
}
]
1 | As the star expression does not include the keyspace name, the results are wrapped in an extra object, and the keyspace name is added to each result. |
SELECT hotel.* FROM hotel LIMIT 5;
[
{ (1)
"address": "Capstone Road, ME7 3JE",
"alias": null,
"checkin": null,
// ...
}
]
1 | As the star expression includes the keyspace name, the keyspace name is not added to the results. |
SELECT meta().id, email, city, phone, hotel.reviews[0].ratings
FROM hotel LIMIT 5;
[
{ (1)
"city": "Medway",
"email": null,
"id": "hotel_10025",
"phone": "+44 870 770 5964",
"ratings": {
"Cleanliness": 5,
"Location": 4,
"Overall": 4,
"Rooms": 3,
"Service": 5,
"Value": 4
}
},
// ...
]
1 | With a select expression, you may optionally include the keyspace name; in either case, the keyspace name is not added to the results. |