FROM Clause

reference

The FROM clause specifies the documents to be used as the input for a query.

Purpose

The FROM clause is used within a SELECT query or subquery. It specifies the documents to be used as the input for a query.

Prerequisites

For you to select data from keyspace or expression, you must have the query_select privilege on that keyspace. For more details about user roles, see Authorization.

Syntax

from-clause ::= FROM from-term

FROM Term

from-term ::= from-keyspace | from-subquery | from-generic | join-clause | nest-clause | unnest-clause

from-keyspace | from-subquery | from-generic | join-clause | nest-clause | unnest-clause

The FROM term may be any of the following:

A keyspace identifier
A subquery (such as derived tables)
A generic expression (nested collections, CURL(), or other expressions)
A JOIN clause and conditions
A NEST clause and conditions
An UNNEST clause and conditions

The JOIN clause, NEST clause, and UNNEST clause each contain a recursive FROM term. This enables you to chain JOIN clauses, NEST clauses, and UNNEST clauses together with each other, or with another type of FROM term.

Limitations

When the FROM term is an expression, USE KEYS or USE INDEX clauses are not allowed.
When using a lookup JOIN clause, an index JOIN clause, a NEST clause, or an UNNEST clause, the left-hand side of the join may be a keyspace identifier, an expression, or a subquery; but the right-hand side may only be a keyspace identifier.
In Couchbase Server 6.5 and later, when using an ANSI JOIN clause, the right-hand side of the join may also be a keyspace identifier, an expression, or a subquery, similar to the left-hand side.

FROM Keyspace

The FROM keyspace specifies a keyspace to query from: either a specific bucket or a constant expression.

Syntax

from-keyspace ::= keyspace-ref [ [ AS ] alias ] [ use-clause ]

Keyspace Reference

keyspace-ref ::= [ namespace ':' ] keyspace

namespace: (Optional) The name or identifier of the namespace of the data source.
keyspace: (Required) The name or identifier of the keyspace of the data source.

The namespace and keyspace identifiers are not dependent on any of the variables in scope of a subquery.

AS Alias

Assigns another name to the FROM keyspace. For details, see AS Clause.

Assigning an alias is optional for the FROM keyspace. If you assign an alias to the FROM keyspace, the AS keyword may be omitted.

USE Clause

Enables you to specify that the query should use particular keys, or a particular index. For details, see USE clause.

Examples

The simplest type of FROM keyspace clause specifies a single bucket.

Example 1. Use a keyspace from a single bucket

Select four unique landmarks from the `travel-sample` bucket.

SELECT DISTINCT name
FROM `travel-sample`
WHERE type = "landmark"
LIMIT 4;

Results

[
  {
    "name": "Royal Engineers Museum"
  },
  {
    "name": "Hollywood Bowl"
  },
  {
    "name": "Thai Won Mien"
  },
  {
    "name": "Spice Court"
  }
]

FROM Subquery

Specifies a N1QL SELECT expression of input objects.

Syntax

from-subquery ::= subquery-expr [ AS ] alias

Subquery Expression

subquery-expr ::= '('  select ')'

Use parentheses to specify a subquery.

For more details and examples, see SELECT Clause and Subqueries.

AS Alias

Assigns another name to the subquery. For details, see AS Clause.

Assigning an alias is required for subqueries in the FROM term. However, when you assign an alias to the subquery, the AS keyword may be omitted.

Examples

Example 2. A SELECT clause inside a FROM clause.

List all Gillingham landmark names from a subset of all landmark names and addresses.

SELECT name, city
FROM (SELECT id, name, address, city
      FROM `travel-sample`
      WHERE type = "landmark") as Landmark_Info
WHERE city = "Gillingham";

Results

[
  {
    "city": "Gillingham",
    "name": "Royal Engineers Museum"
  },
  {
    "city": "Gillingham",
    "name": "Hollywood Bowl"
  },
  {
    "city": "Gillingham",
    "name": "Thai Won Mien"
  },
  {
    "city": "Gillingham",
    "name": "Spice Court"
  },
  {
    "city": "Gillingham",
    "name": "Beijing Inn"
  },
  {
    "city": "Gillingham",
    "name": "Ossie's Fish and Chips"
  }
]

Example 3. Subquery Example

For each country, find the number of airports at different altitudes and their corresponding cities.

In this case, the inner query finds the first level of grouping of different altitudes by country and corresponding number of cities. Then the outer query builds on the inner query results to count the number of different altitude groups for each country and the total number of cities.

SELECT t1.country, num_alts, total_cities
FROM (SELECT country, geo.alt AS alt,
             count(city) AS num_cities
      FROM `travel-sample`
      WHERE type = "airport"
      GROUP BY country, geo.alt) t1
GROUP BY t1.country
LETTING num_alts = count(t1.alt), total_cities = sum(t1.num_cities);

Results

[
  {
    "country": "United States",
    "num_alts": 946,
    "total_cities": 1560
  },
  {
    "country": "United Kingdom",
    "num_alts": 128,
    "total_cities": 187
  },
  {
    "country": "France",
    "num_alts": 196,
    "total_cities": 221
  }
]

This is equivalent to blending the results of the following two queries by country, but the subquery in the from-term above simplified it.

SELECT country,count(city) AS num_cities
FROM `travel-sample`
WHERE type = "airport"
GROUP BY country;

SELECT country, count(distinct geo.alt) AS num_alts
FROM `travel-sample`
WHERE type = "airport"
GROUP BY country;

FROM Generic Expression

Couchbase Server version 4.6.2 added support for generic expressions in the FROM term; and this adds huge flexibility by the enabling of various N1QL functions, operators, path expressions, language constructs on constant expressions, variables, and subqueries to create just about any FROM clause imaginable.

Syntax

from-generic ::= expr [ AS alias ]

expr: A N1QL expression generating JSON documents or objects.

AS Alias

Assigns another name to the generic expression. For details, see AS Clause.

Assigning an alias is optional for generic expressions in the FROM term. However, when you assign an alias to the expression, the AS keyword is required.

Examples

Example 4. Independent Constant Expression

The expression may include JSON scalar values, static JSON literals, objects, or N1QL functions.

SELECT * FROM [1, 2, "name", { "type" : "airport", "id" : "SFO"}]  AS  ks1;

SELECT CURL("https://maps.googleapis.com/maps/api/geocode/json",
           {"data":"address=Half+Moon+Bay" , "request":"GET"} );

Note that functions such as CURL() can independently produce input data objects for the query. Similarly, other N1QL functions can also be used in the expressions.

Example 5. Variable N1QL Expression

The expression may refer to any variables in scope for the query.

SELECT count(*)
FROM `travel-sample` t
LET x = t.geo
WHERE (SELECT RAW y.alt FROM x y)[0] > 6000;

The FROM x clause is an expression that refers to the outer query. This is applicable to only subqueries because the outermost level query cannot use any variables in its own FROM clause. This makes the subquery correlated with outer queries, as explained in the Subqueries section.

AS Clause

To use a shorter or clearer name anywhere in the query, like SQL, N1QL allows you to assign an alias to any FROM term in the FROM clause.

Syntax

The AS keyword is required when assigning an alias to a generic expression.

The AS keyword is optional when assigning an alias to the FROM keyspace, a subquery, the JOIN clause, the NEST clause, or the UNNEST clause.

Arguments

alias: String to assign an alias.

Since the original name may lead to referencing wrong data and wrong results, you must use the alias name throughout the query instead of the original keyspace name.

In the FROM clause, the renaming appears only in the projection and not the fields themselves.

When no alias is used, the keyspace or last field name of an expression is given as the implicit alias.

When an alias conflicts with a keyspace or field name in the same scope, the identifier always refers to the alias. This allows for consistent behavior in scenarios where an identifier only conflicts in some documents. For more information on aliases, see Identifiers.

Examples

The following FROM clauses are equivalent, with and without the AS keyword.

FROM `travel-sample` AS t

FROM `travel-sample` t

FROM `travel-sample` AS h
INNER JOIN `travel-sample` AS l
ON (h.city = l.city)

FROM `travel-sample` h
INNER JOIN `travel-sample` l
ON (h.city = l.city)

FROM Clause

Purpose

Prerequisites

Syntax

FROM Term

Limitations

FROM Keyspace

Syntax

Keyspace Reference

AS Alias

USE Clause

Examples

FROM Subquery

Syntax

Subquery Expression

AS Alias

Examples

FROM Generic Expression

Syntax

AS Alias

Examples

AS Clause

Syntax

Arguments

Examples

Related Links