SELECT clause

SELECT clause

Use the SELECT statement to return a ResultSet of matching documents.

Syntax

SELECT [ALL|DISTINCT] [RAW|ELEMENT] [keyspace_name.] field_name [AS alias_name]
 [FROM keyspace_name [AS alias_name] ]
[WHERE where_clause] [AND where_clause2]
[GROUP BY group_clause]
[UNION|INTERSECT|EXCEPT union_clause]
[ORDER BY order_clause]
[LIMIT limit_int]
[OFFSET offset_clause]
;
Arguments
ALL | DISTINCT
[Optional; default is ALL]
SELECT ALL retrieves all of the data specified and will display all of the specified columns, including all duplicates.
SELECT DISTINCT removes duplicate result objects from the query's result set.
Note: Since Couchbase Server 4.5.1, the DISTINCT clause is no longer blocking in nature since it streams the input and produces the output in-parallel, while consuming less memory.
RAW | ELEMENT
[Optional; RAW and ELEMENT are synonyms]
SELECT RAW specifies to make the resulting expression not be qualified; that is, the standard N1QL output (same as relational engines) returns tuples tagged with an alias, and the RAW qualifier removes this extra layer. For details, see SELECT RAW.
keyspace_name
[Optional; default is specified by the FROM clause] The name of the keyspace (equivalent to a relational database table).
Multiple keyspaces are separated by a comma.
Note: When specifying the keyspace name in SELECT keyspace_name.* for all fields, the keyspace name will not appear in the result set; whereas not specifying the keyspace name in SELECT * FROM keyspace_name adds the keyspace name to the result set.
SELECT * FROM `travel-sample` WHERE type="hotel";

[
  {
    "travel-sample": {                          / added line with keyspace
      "address": "Capstone Road, ME7 3JE",
      "alias": null,
      "checkin": null,
...
SELECT `travel-sample`.* FROM `travel-sample` WHERE type="hotel";

[
  {                                             / no added line with keyspace
    "address": "Capstone Road, ME7 3JE",
    "alias": null,
    "checkin": null,
...
SELECT meta().id,email,city,phone,`travel-sample`.reviews[0].ratings FROM `travel-sample` WHERE type="hotel" LIMIT 5;

[
  {                                             / no added line with keyspace
    "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
    }
  },
field_name
The name of the field or fields, separated by a comma, to be in the query's ResultSet, such as:
SELECT id, airline, stops FROM `travel-sample` WHERE type="route";
To use a field within an array, use [0] after the array name, followed by a period and the field name, such as:
SELECT schedule[0].day FROM `travel-sample` WHERE type="route";
AS alias_name
A temporary name of a bucket name or field name to make names more readable or unique, such as:
SELECT schedule[0].day AS Weekday
FROM keyspace_name
[Optional] The name of the keyspace (equivalent to a relational database table), internal N1QL system keyspace, file system storage, or micro service that defines the source of input documents or objects for the query.
Multiple keyspaces are separated by a comma and can be aliased, such as:
SELECT * FROM `travel-sample` AS ts, `beer-sample` AS bs;
For details, see FROM clause.
WHERE where_clause AND where_clause2
[Optional] The clause or set of clauses used to filter the documents. Use AND between where clauses, such as:
SELECT * FROM `travel-sample` WHERE type="route" AND airline="UA";
For details, see WHERE clause.
GROUP BY group_clause
[Optional] The clause to organize a ResultSet by one or more expressions and can be used in aggregate functions, such as:
SELECT * FROM `travel-sample` GROUP BY type;
For details, see GROUP BY clause.
UNION | INTERSECT | EXCEPT union_clause
[Optional] The clause to combine results from multiple sub-selects, such as:
SELECT name, city FROM `travel-sample` WHERE type="route" AND city="San Francisco"
UNION SELECT name, city FROM `beer-sample` WHERE type="brewery" AND city="San Francisco";
For details, see UNION, INTERSECT, and EXCEPT.
ORDER BY order_clause
[Optional] The field or group of fields to order the ResultSet, such as:
SELECT * FROM `travel-sample` ORDER BY city;
For details, see ORDER BY clause.
LIMIT limit_clause
[Optional] The maximum number of objects to be returned in the ResultSet, such as:
SELECT * FROM `travel-sample` LIMIT 10;
For details, see LIMIT clause.
OFFSET offset_clause
[Optional] The number of objects to be skipped before starting a ResultSet, such as:
SELECT * FROM `travel-sample` LIMIT 10 OFFSET 200;
For details, see OFFSET clause.
;
All SELECT statements must end with a semi-colon.