SELECT Clause

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

In the below SELECT statement, the SELECT clause is boldfaced:

SELECT select_clause
[FROM from_clause] [JOIN join_clause]
[USE INDEX useindex_clause]
[LET let_clause]
[WHERE where_clause ( [AND where_clause2] )* ]
[GROUP BY groupby_clause] [LETTING|HAVING letting_clause]
[UNION|INTERSECT|EXCEPT union_clause]
[ORDER BY orderby_clause]
[LIMIT limit_int]
[OFFSET offset_clause]
;

Syntax of the select_clause

SELECT [ALL|DISTINCT] ( result-expr [ , result-expr2 ]*
| (RAW|ELEMENT|VALUE) expr [ [AS] alias ] )

Railroad Diagrams for SELECT clause:

select clause

result-expr:

select clause RR result expr

path:

select clause RR path

alias:

select clause RR alias

Arguments

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.

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.

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

Q1: SELECT city FROM `travel-sample`;

slightly slower

slightly faster

Q2: SELECT META().id FROM `travel-sample`;

much faster

much slower

In the below EXPLAIN example of using DISTINCT, the lines using the DISTINCT operator are boldfaced:

EXPLAIN SELECT DISTINCT city FROM `travel-sample`;

Results:
{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "PrimaryScan",
        "index": "#primary",
        "keyspace": "travel-sample",
        "namespace": "default",
        "using": "gsi"
      },
      {
        "#operator": "Fetch",
        "keyspace": "travel-sample",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "distinct": true,
              "result_terms": [
                {
                  "expr": "(`travel-sample`.`city`)"
                }
              ]
            },
            {
              "#operator": "Distinct"
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      },
      {
        "#operator": "Distinct"
      }
    ]
  },
  "text": "SELECT DISTINCT city\nFROM `travel-sample`;"
}
result-expr

One or more expressions that evaluate to one or more field names to be in the ResultSet.

If no field name is specified, the input for the query is a single empty object that allows you to perform calculations with the SELECT statement, such as SELECT 10+20 AS Total; or other N1QL expression.

For details with examples, see N1QL Expressions.

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 1: Comparing SELECT and SELECT RAW on a basic query:

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

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

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 2: Comparing SELECT and SELECT RAW listing 5 airport cities alphabetically:

SELECT city

FROM `travel-sample`

WHERE type="airport"

ORDER BY city LIMIT 5;

SELECT RAW city

FROM `travel-sample`

WHERE type="airport"

ORDER BY city LIMIT 5;

SELECT DISTINCT RAW city

FROM `travel-sample`

WHERE type="airport"

ORDER BY city LIMIT 5;
select clause ex2
keyspace_name1, keyspace_name2, ..., keyspace_nameX

[Optional; if the keyspace is unspecified in the SELECT clause, it will be taken from the FROM clause]

The name of the keyspace or keyspaces used, separated by a comma.

Keyspaces map to buckets in Couchbase Server. A keyspace is a set of documents that may vary in structure and are a unit of authorization and resource allocation.

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";

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

Results:
[
  {                                             / 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;

Results:
[
  {                                             / 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_expr

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

Best Practices

When possible, explicitly list all fields you want in your ResultSet instead of the "" 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 field names).

Examples

Example 1: Select all the fields of 1 document of type airline from the travel-sample keyspace.

SELECT * FROM `travel-sample` WHERE type="airline" LIMIT 1;

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

Example 2: Select all the fields of 1 document of type landmark from the travel-sample keyspace.

SELECT * FROM `travel-sample` WHERE type="landmark" LIMIT 1;

Results:
[
  {
    "travel-sample": {
      "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"
    }
  }
]

FROM clause

USE INDEX clause

LET Clause

WHERE Clause

GROUP BY Clause

UNION, INTERSECT, and EXCEPT Clause