SELECT Clause

reference

The SELECT clause determines the result set.

Purpose

In a SELECT statement, the SELECT clause determines the projection (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

hint-comment	Optimizer Hints
projection	Projection

Optimizer Hints

(Introduced in Couchbase Server 7.1)

In Couchbase Server 7.1 and later, 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 )? )

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.

In the below table,

Q1's 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.
On the other extreme, Q2's SELECT DISTINCT does not reduce the recordset at all since META().id is already unique, and thus projects the entire original recordset and shows the maximum overhead of about twice as long to execute than SELECT ALL.

N1QL Query SELECT ALL SELECT DISTINCT

N1QL Query	SELECT ALL	SELECT DISTINCT
Q1 SELECT city FROM `travel-sample`.inventory.landmark;	slightly slower	slightly faster
Q2 SELECT META().id FROM `travel-sample`.inventory.landmark;	much faster	much slower

SELECT city FROM `travel-sample`.inventory.landmark;

slightly slower

slightly faster

SELECT META().id FROM `travel-sample`.inventory.landmark;

much faster

much slower

Example 1. Query plan using the DISTINCT operator

Query

EXPLAIN SELECT DISTINCT city FROM `travel-sample`.inventory.landmark; (1)

Results

[
  {
    "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 `travel-sample`.inventory.landmark;"
  }
]

1	Lines using the `DISTINCT` operator

Result Expression

result-expr ::= ( path '.' )? '*' | expr ( 'AS'? alias )?

path ::= identifier ( '[' expr ']' )* ( '.' identifier ( '[' expr ']' )* )*

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.)

SELECT RAW reduces the amount of data returned by eliminating the field attribute.

Example 2. Comparing SELECT and SELECT RAW on a basic query

Query

SELECT {"a":1, "b":2};

Query

SELECT RAW {"a":1, "b":2};

Results

[
  {
    "$1": { (1)
      "a": 1,
      "b": 2
    }
  }
]

Results

[
  { (2)
    "a": 1,
    "b": 2
  }
]

1	Added alias
2	No added alias

There are times in which this extra layer might not be desirable since it requires extra output parsing. So the RAW qualifier specifies that the expression that follows not to be qualified, as shown in the next example.

Example 3. Comparing SELECT and SELECT RAW listing 5 airport cities alphabetically

Query

SELECT city
FROM `travel-sample`.inventory.airport
ORDER BY city LIMIT 5;

Query

SELECT RAW city
FROM `travel-sample`.inventory.airport
ORDER BY city LIMIT 5;

Query

SELECT DISTINCT RAW city
FROM `travel-sample`.inventory.airport
ORDER BY city LIMIT 5;

Results

[
  {
    "city": "Abbeville"
  },
  {
    "city": "Aberdeen"
  },
  {
    "city": "Aberdeen"
  },
  {
    "city": "Aberdeen"
  },
  {
    "city": "Abilene"
  }
]

Results

[
  "Abbeville",
  "Aberdeen",
  "Aberdeen",
  "Aberdeen",
  "Abilene"
]

Results

[
  "Abbeville",
  "Aberdeen",
  "Abilene",
  "Adak Island",
  "Addison"
]

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 or select expression, as in Query A, adds the keyspace name to the result set; whereas if you include the keyspace name before a star expression or select expression, as in Query B and Query C, the keyspace name will not appear in the result set.

Query A

SELECT * FROM `travel-sample`.inventory.hotel;

Results

[
  {
    "hotel": { (1)
      "address": "Capstone Road, ME7 3JE",
      "alias": null,
      "checkin": null,
// ...
    }
  }
]

Query B

SELECT hotel.* FROM `travel-sample`.inventory.hotel;

Results

[
  { (2)
    "address": "Capstone Road, ME7 3JE",
    "alias": null,
    "checkin": null,
// ...
  }
]

Query C

SELECT meta().id, email, city, phone, hotel.reviews[0].ratings
FROM `travel-sample`.inventory.hotel LIMIT 5;

Results

[
  { (3)
    "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	Added line with keyspace
2	No added line with keyspace
3	No added line with keyspace

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. For example:

Query

SELECT id, airline, stops FROM `travel-sample`.inventory.route;

The select expression may include a path, to select a nested field from within an array. For example:

Query

SELECT schedule[0].day FROM `travel-sample`.inventory.route;

If no field name is specified, the select expression allows you to perform calculations, such as SELECT 10+20 AS Total; or any other N1QL expression.

For details with examples, see N1QL Expressions.

AS Alias

alias ::= identifier

A temporary name of a keyspace name or field name to make names more readable or unique, such as:

Query

SELECT schedule[0].day AS Weekday FROM `travel-sample`.inventory.route;

If you do not explicitly give a field an alias, it is given an implicit alias in the result set.

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.

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

Example 4. Select all the fields of 1 document from the airline keyspace

Query

SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;

Results

[
  {
    "airline": {
      "callsign": "MILE-AIR",
      "country": "United States",
      "iata": "Q5",
      "icao": "MLA",
      "id": 10,
      "name": "40-Mile Air",
      "type": "airline"
    }
  }
]

Example 5. Select all the fields of 1 document from the landmark keyspace

Query

SELECT * FROM `travel-sample`.inventory.landmark LIMIT 1;

Results

[
  {
    "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 Clause

Purpose

Prerequisites

Syntax

Optimizer Hints

Projection

ALL / DISTINCT

Result Expression

RAW / ELEMENT / VALUE

Star Expression (*)

Select Expression

AS Alias

Best Practices

Examples

Related Links