FROM clause

The FROM clause defines the keyspaces, input documents, or objects for the query.

(Introduced in Couchbase Server 1.0)

Purpose

In a SELECT query or subquery, the FROM clause defines the keyspaces and the source of input documents or objects for the query. Every FROM clause specifies one or more keyspaces. The first keyspace is called the primary keyspace and is an optional clause for your query.

If the FROM clause is omitted, the input for the query is a single empty object that allows you to perform calculations with the SELECT statement.

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

FROM from-keyspace [ [ AS ] alias1 ] [ USE KEYS use-clause ]
                    | "(" SELECT ")" [ [ AS ] alias2 ]
                    | expr [ [ AS ] alias3 ]
                    | from-term ( join-clause | nest-clause | unnest-clause )

Couchbase Server version 4.x supports only keyspace identifier or a subquery in the from-term, but not expressions. Couchbase Server version 4.6.2 adds support for generic expression in the from-term.

Arguments

from-keyspace: [Optional] String or expression resulting in a string that represents the keyspace or source of input documents. For details, see Keyspaces.
alias (Optionally, AS alias): [Optional] To assign another name. For details, see AS Keyword.
USE KEYS: [Optional] To specify one or multiple primary keys. For details, see USE KEYS Clause.
SELECT: [Optional] To create a subquery of input objects. For details, see SELECT Clause.
expr: [Optional] A string expression of input objects. For details, see N1QL Expressions.
from-term: [Optional] The from-term defines the input objects for the query and it can be either a keyspace identifier or an expression.

When using nested subqueries, the from-term of the outermost parent query can only have a keyspace identifier, a subquery, or a constant expression since the from-term needs to independently produce input documents for the query.

However, the subqueries can use generic variable expressions, which may be dependent on the Variables in Scope of a Subquery. For details, see Subqueries.
join-clause: [Optional] To create new input objects by combining two or more source objects either via a Lookup JOIN or an Index JOIN.
nest-clause: [Optional] To produce a single result of joining two keyspaces. For details, see NEST Clause.
unnest-clause: [Optional] To perform a join of the nested array with its parent. For details, see UNNEST Clause.

from-term::= keyspace-ref [ [ AS ] alias ] [ use-keys-clause ]
                        | "(" select ")" [ AS ] alias
                        | expr [ AS ] alias
                        | from-term ( join-clause | nest-clause | unnest-clause )

from-path:

[ namespace : ] path

namespace:

identifier

expr:

see N1QL expression

use-keys-clause:

USE [ PRIMARY ] KEYS expression

join-clause:

( lookup-join | index-join )

lookup-join:

[ join-type ] JOIN from-path [ [ AS ] alias ] on-keys-clause

index-join:

[ join-type ] JOIN from-path [ [ AS ] alias ] ( on-keys-clause | on-key-for-clause )

join-type:

INNER | LEFT [ OUTER ]

on-keys-clause:

ON [ PRIMARY ] KEYS expression

on-key-for-clause:

ON [ PRIMARY ] KEY rhs-expression.lhs-expression-key FOR lhs-expression

rhs-expression: keyspace or expression corresponding to the right hand side of JOIN.

lhs-expression: keyspace or expression corresponding to the left hand side of JOIN.

lhs-expression-key: attribute in rhs-expression referencing primary key for lhs-expression.

nest-clause:

[ join-type ] NEST from-path [ ( [ AS ] alias ) ] on-keys-clause

unnest-clause:

[ join-type ] [ UNNEST | FLATTEN ] expression [ ( [ AS ] alias ) ]

Omitted FROM clause

If the FROM clause is omitted, the data source is equivalent to an array containing a single empty object. This allows you to evaluate expressions that do not depend on stored data.

Evaluating an expression SELECT 10 + 20 produces the following result:

[ { "$1" : 30 } ]

Counting the number of inputs SELECT COUNT(*) AS input_count produces the following result:

[ { "input_count" : 1 } ]

Getting the input contents SELECT * produces the following result:

[ { } ]

Omitted FROM Clause

If the FROM clause is omitted, the data source is equivalent to an array containing a single empty object. This allows you to evaluate expressions that do not depend on stored data.

Omitted FROM Example 1: Evaluating an expression.

Add the two numbers 10 and 20 together.

SELECT 10 + 20

This results in:

[
  {
    "$1" : 30
  }
]

Omitted FROM Example 2: Selecting the local time.

Select the local time and format the result as "yyyy-mm-dd".

SELECT NOW_LOCAL('1111-11-11')

This results in:

[
  {
    "$1": "2017-11-30"
  }
]

Omitted FROM Example 3: Calculating the days until Christmas.

Use the DATE_DATE_STR function to calculate the days from now until Christmas.

SELECT DATE_DIFF_STR('2017-12-25',NOW_LOCAL('1111-11-11'),'day')

This results in:

[
  {
    "$1": 25
  }
]

Keyspaces

The simplest type of FROM clause specifies a keyspace:

SELECT * FROM `travel-sample`;

This returns every value in the `travel-sample` keyspace.

The keyspace can be prefixed with an optional namespace (pool):

SELECT * FROM main:`travel-sample`;

This queries the customer keyspace in the main namespace.

If the namespace is omitted, the default namespace in the current session is used.

Keyspace Identifier

This is the name or identifier of an independent keyspace that can serve as a data source or keyspace of one or more documents. Such keyspaces are not dependent on any of the Variables in Scope of a Subquery.

Keyspace Example 1: Use the travel-sample keyspace.

Select 4 unique landmarks from the keyspace `travel-sample`.

SELECT t1.city
FROM `travel-sample` t1
WHERE t1.type = "landmark" LIMIT 4;

This results in:

[
  {
    "city": "Gillingham"
  },
  {
    "city": "Giverny"
  },
  {
    "city": "Glasgow"
  },
  {
    "city": "Clarkston"
  }
]

Keyspace Example 2: Cities that have landmarks and airports. (non-correlated)

The following non-correlated subquery uses the keyspace `travel-sample` independent of the same keyspace used in the outer query:

SELECT DISTINCT t1.city
FROM `travel-sample` t1
WHERE t1.type = "landmark"
AND   t1.city IN (SELECT RAW city
                  FROM `travel-sample`
                  WHERE type = "airport")
LIMIT 3;

This results in:

[
  {
    "city": "Avignon"
  },
  {
    "city": "Bangor"
  },
  {
    "city": "Glasgow"
  }
]

Keyspace Example 3: Cities that have museum landmarks. (correlated)

The following correlated subquery uses the keyspace alias from an outer query:

SELECT t1.city, t1.name
FROM `travel-sample` t1
WHERE t1.type = "landmark"
AND   (SELECT RAW t2
       FROM SPLIT(t1.name) t2
       WHERE t2 = "museum")[0] IS NOT NULL
LIMIT 3;

This results in:

[
  {
    "city": "Washington",
    "name": "Washington 'F' Pit mining museum"
  },
  {
    "city": "L'Isle-sur-la-Sorgue",
    "name": "Campredon museum"
  },
  {
    "city": "Isle of Anglesey",
    "name": "Swtan heritage museum"
  }
]

Keyspace Example 3: Cities that have landmarks and breweries.

The following subquery example uses different keyspaces in the outer and inner queries.

SELECT DISTINCT t1.city
FROM `travel-sample` t1
WHERE t1.type = "landmark"
AND   t1.city IN (SELECT RAW b1.city
                  FROM `beer-sample` b1
                  WHERE b1.type = "brewery")
LIMIT 3;

This results in:

[
  {
    "city": "Bangor"
  },
  {
    "city": "East Lothian"
  },
  {
    "city": "Escondido"
  }
]

N1QL Expression

Couchbase Server version 4.6.2 adds support for generic expressions in the from-term. This is a very powerful functionality as it enables usage of various N1QL functions, operators, path expressions, language constructs on constant expressions, variables, and subqueries.

When the from-term is an expression, USE KEYS or USE INDEX clauses are not allowed.
When using a JOIN clause, NEST clause, or UNNEST clause, the left-side keyspace can be an expression or subquery, but the right-side keyspace must be a keyspace identifier.
Independent Constant Expression
This includes any N1QL expressions of JSON scalar values, static JSON literals, objects, or N1QL functions. For example:
SELECT * FROM [1, 2, "name", { "type" : "airport", "id" : "SFO"}] AS ks1; SELECT * FROM CURL(...) as ks2;
Note that functions such as CURL() can independently produce input data objects for the query. However, other N1QL functions can also be used in the expressions.
Variable N1QL Expression

This includes expressions that refers to any variables in scope for the 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.

Subquery and Subquery Expressions
A subquery itself can appear as a from-term expression. In this case, the subquery results are fed as data source to the outer query. Further, subqueries can occur as a subquery or as constituent part of a bigger N1QL expression.

Subquery Example 1a: Highest altitude airport/city in each country. (with a subquery)

The following shows a simple case of using a subquery in the FROM clause:
SELECT t1.country, t1.max_country_alt, ARRAY x.city FOR x IN t1.c WHEN x.alt = t1.max_country_alt END FROM (SELECT country, array_agg({"alt": geo.alt , city}) c, max_country_alt FROM `travel-sample` WHERE type = "airport" GROUP BY country LETTING max_country_alt = max(geo.alt) ) t1;
(in 107ms) This results in:
[ { "$1": [ "Shaftesbury" ], "country": "United Kingdom", "max_country_alt": 811 }, { "$1": [ "Courcheval" ], "country": "France", "max_country_alt": 6588 }, { "$1": [ "Telluride" ], "country": "United States", "max_country_alt": 9078 } ]
Subquery Example 1b: Highest altitude airports/cities in each country. (without a subquery)

The same query as above, but use N1QL’s power to express it without a subquery.
SELECT country, (ARRAY x.city FOR x IN array_agg({"alt": geo.alt, city}) WHEN x.alt = max_country_alt END) AS max_alt_city, max_country_alt FROM `travel-sample` WHERE type = "airport" GROUP BY country LETTING max_country_alt = max(geo.alt);
(in 104ms) This results in:
[ { "country": "United Kingdom", "max_alt_city": [ "Shaftesbury" ], "max_country_alt": 811 }, { "country": "France", "max_alt_city": [ "Courcheval" ], "max_country_alt": 6588 }, { "country": "United States", "max_alt_city": [ "Telluride" ], "max_country_alt": 9078 } ]
A more realistic example of needing subquery in the FROM clause arises in more complicated scenarios, such as when multiple levels of aggregates (sort orders and limits) are required, or when the subquery results of one keyspace may need to be JOINed with another keyspace.

Subquery Example 2: 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);
This results in:
[ { "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;
See Subqueries for details and examples.
Nested Path Expressions
Expressions used in the from-term can have nested paths, including constant or variable or subquery expressions. Similarly, variable expressions are allowed in only subqueries (not in outermost parent queries).

Further, the nested-path variable expression in a subquery from-term must resolve to variables/aliases in scope, and not to any keyspace identifiers. Otherwise, it results in a syntax error.

When an expression cannot be resolved to any variables in scope, it is considered keyspace identifier. See Nested Paths in Subqueries for more details.

Nested Example 1: Usage of a nested path over a subquery expression.
SELECT x.geo.alt FROM (SELECT geo from `travel-sample` WHERE type = "airport") AS x LIMIT 2;
This results in:
[ { "alt": 12 }, { "alt": 295 } ]
Nested Example 2: Usage of a nested path over a constant expression.
SELECT x.b FROM [{"a" : 1, "b" : {"c" : 2}}, {"a" : 3, "b" : {"d" : 4}}] AS x LIMIT 2;
This results in:
[ { "x": { "c": 2 } }, { "x": { "d": 4 } } ]

USE KEYS Clause

Specific primary keys within a keyspace can be specified. Only values having those primary keys will be included as inputs to the query.

Syntax

USE [ PRIMARY ] KEYS key_expression

Arguments

PRIMARY: [Optional] Make the key_expression a unique Primary Key.
key_expression: String of one or more fields to be used as a key.

USE KEYS Example 1: Specify a single key.

SELECT * FROM customer USE KEYS "acme-uuid-1234-5678"

USE KEYS Example 2: Specify multiple keys.

SELECT * FROM customer USE KEYS [ "acme-uuid-1234-5678", "roadster-uuid-4321-8765" ]

In the FROM clause of a subquery, USE KEYS is mandatory for the primary keyspace.

Lookup JOIN Clause

(Introduced in Couchbase Server 4.5)

The JOIN clause enables you to create new input objects by combining two or more source objects.

Lookup joins allow only left-to-right joins, which means each qualified document from the left-hand side (LHS) of the JOIN operator is required to produce primary keys of documents on the right-hand side (RHS). These keys are subsequently nested-loop-joined to access qualified RHS documents.

Syntax

[ join-type ] JOIN from-path [ [ AS ] alias ] ON KEYS on-keys-clause

Arguments

join-type

[Optional; default is LEFT INNER]

LEFT or LEFT INNER: For each joined object produced, both the left-hand and right-hand source objects must be non-MISSING and non-NULL.
LEFT OUTER: For each joined object produced, only the left-hand source objects must be non-MISSING and non-NULL.

from-path

[Required] String or expression resulting in a string that represents the keyspace or source of input documents. For details, see Keyspaces.

alias (Optionally, AS alias)

[Optional] To assign another name. For details, see AS Keyword.

ON KEYS on-keys-clause

[Required] String or expression representing the primary keys of the documents for the second keyspace.

The ON KEYS expression produces one or more document keys for the right-hand side document.

The ON KEYS expression can produce an array of document keys.

Return Values

If LEFT or LEFT OUTER is specified, then a left outer join is performed.

At least one joined object is produced for each left-hand source object.

If the right-hand source object is NULL or MISSING, then the joined object’s right-hand side value is also NULL or MISSING (omitted), respectively.

Limitations

Lookup JOINs cannot be chained together.

Lookup JOIN Example 1: route JOIN airline ON KEYS route.airlineid.

List all airlines and non-stop routes from SFO in the travel-sample keyspace.

SELECT DISTINCT airline.name, airline.callsign, route.destinationairport, route.stops, route.airline
FROM `travel-sample` route
  JOIN `travel-sample` airline
  ON KEYS route.airlineid
WHERE route.type = "route"
AND airline.type = "airline"
AND route.sourceairport = "SFO"
AND route.stops = 0
LIMIT 4;

This results in:

[
  {
    "airline": "VX",
    "callsign": "REDWOOD",
    "destinationairport": "SAN",
    "name": "Virgin America",
    "stops": 0
  },
  {
    "airline": "VX",
    "callsign": "REDWOOD",
    "destinationairport": "PHL",
    "name": "Virgin America",
    "stops": 0
  },
  {
    "airline": "B6",
    "callsign": "JETBLUE",
    "destinationairport": "FLL",
    "name": "JetBlue Airways",
    "stops": 0
  },
  {
    "airline": "UA",
    "callsign": "UNITED",
    "destinationairport": "IND",
    "name": "United Airlines",
    "stops": 0
  }
]

Lookup JOIN Example 2: route JOIN airline ON KEYS route.airlineid.

List the schedule of flights from Boston to San Francisco on JETBLUE in the travel-sample keyspace.

SELECT DISTINCT airline.name, route.schedule
FROM `travel-sample` route
  JOIN `travel-sample` airline
  ON KEYS route.airlineid
WHERE route.type = "route"
AND airline.type = "airline"
AND route.sourceairport = "BOS"
AND route.destinationairport = "SFO"
AND airline.callsign = "JETBLUE";

This results in:

[
  {
    "name": "JetBlue Airways",
    "schedule": [
      {
        "day": 0,
        "flight": "B6076",
        "utc": "10:15:00"
      },
      {
        "day": 0,
        "flight": "B6321",
        "utc": "00:06:00"
      },
      {
        "day": 1,
        "flight": "B6536",
        "utc": "22:45:00"
      },
      {
        "day": 1,
        "flight": "B6194",
        "utc": "00:51:00"
      },
      {
        "day": 2,
        "flight": "B6918",
        "utc": "23:45:00"
      },
      {
        "day": 2,
        "flight": "B6451",
        "utc": "18:09:00"
      },
      {
        "day": 2,
        "flight": "B6868",
        "utc": "22:04:00"
      },
      {
        "day": 2,
        "flight": "B6621",
        "utc": "11:04:00"
      },
      {
        "day": 3,
        "flight": "B6015",
        "utc": "16:59:00"
      },
      {
        "day": 3,
        "flight": "B6668",
        "utc": "07:22:00"
      },
      {
        "day": 3,
        "flight": "B6188",
        "utc": "01:41:00"
      },
      {
        "day": 3,
        "flight": "B6215",
        "utc": "19:35:00"
      },
      {
        "day": 4,
        "flight": "B6371",
        "utc": "21:37:00"
      },
      {
        "day": 4,
        "flight": "B6024",
        "utc": "10:24:00"
      },
      {
        "day": 4,
        "flight": "B6749",
        "utc": "01:12:00"
      },
      {
        "day": 4,
        "flight": "B6170",
        "utc": "01:14:00"
      },
      {
        "day": 5,
        "flight": "B6613",
        "utc": "08:59:00"
      },
      {
        "day": 5,
        "flight": "B6761",
        "utc": "15:24:00"
      },
      {
        "day": 5,
        "flight": "B6162",
        "utc": "02:42:00"
      },
      {
        "day": 5,
        "flight": "B6341",
        "utc": "21:26:00"
      },
      {
        "day": 5,
        "flight": "B6347",
        "utc": "08:43:00"
      },
      {
        "day": 6,
        "flight": "B6481",
        "utc": "22:08:00"
      },
      {
        "day": 6,
        "flight": "B6549",
        "utc": "21:48:00"
      },
      {
        "day": 6,
        "flight": "B6994",
        "utc": "11:30:00"
      },
      {
        "day": 6,
        "flight": "B6892",
        "utc": "13:27:00"
      }
    ]
  }
]

ON KEYS is required after each JOIN. It specifies the primary keys for the second keyspace in the join.

Index JOIN Clause

When using lookup joins, right-to-left joins with RHS documents containing primary key references to LHS documents cannot be joined efficiently using any index.

Consider the above Lookup Example #1 with route and airline documents where route.airlineid is the primary key of route documents and airline documents have no reference to route documents:

SELECT DISTINCT airline.name, airline.callsign, route.destinationairport, route.stops, route.airline
FROM `travel-sample` route
  JOIN `travel-sample` airline
  ON KEYS route.airlineid
WHERE route.type = "route"
AND airline.type = "airline"
AND route.sourceairport = "SFO"
LIMIT 4;

This query gets a list of flights from airlines flying from SFO, but getting flights to SFO cannot be efficiently executed without making a Cartesian product of all route documents (LHS) with all airline documents (RHS).

This query cannot use any index on airline to directly access sourceairport in SFO because airline is on the RHS.

Also, you cannot rewrite the query to put the airline document on the LHS (to use any index) and the route document on the RHS because the airline documents (on the LHS) have no primary keys to access the route documents (on the RHS).

Using index joins, the same query can be written as:

Required Index:
  CREATE INDEX route_airlineid ON `travel-sample`(airlineid) WHERE type="route";

Optional index:
  CREATE INDEX route_sourceairport ON `travel-sample`(sourceairport) WHERE type="route";

Resulting in:
  SELECT * FROM `travel-sample` airline
    JOIN `travel-sample` route
    ON KEY route.airlineid FOR airline
  WHERE route.type="route"
  AND airline.type="airline"
  AND route.sourceairport = "SFO";

If you generalize the same query, it looks like the following:

CREATE INDEX on-key-for-index-name rhs-expression (lhs-expression-key);

SELECT projection-list FROM lhs-expression
  JOIN rhs-expression
  ON KEY rhs-expression.lhs-expression-key FOR lhs-expression
[ WHERE predicates ] ;

There are three important changes in the index scan syntax example above:

CREATE INDEX on the ON KEY expression route.airlineid to access route documents using airlineid (which are produced on the LHS).
The ON KEY route.airlineid FOR airline enables N1QL to use the index route.airlineid.
Create any optional index such as route.sourceairport that can be used on route (LHS).

For index joins, the syntax uses ON KEY (singular) instead of ON KEYS (plural). This is because Index JOINs' ON KEY expression must produce a scalar value; whereas Lookup JOINs' ON KEYS expression can produce either a scalar or an array value.

Syntax

[ join-type ] JOIN from-path [ [ AS ] alias ]  ON KEY FOR on-key-for-clause

Arguments

join-type

[Optional; default is LEFT INNER]

LEFT or LEFT INNER: For each joined object produced, both the left-hand and right-hand source objects must be non-MISSING and non-NULL.
LEFT OUTER: For each joined object produced, only the left-hand source objects must be non-MISSING and non-NULL.

from-path

String or expression resulting in a string that represents the keyspace or source of input documents. For details, see Keyspaces.

AS alias

[Optional] To assign another name. For details, see AS Keyword.

ON [PRIMARY] KEY rhs-expression.lhs-expression-key FOR lhs-expression

[Optional]

rhs-expression: String or expression representing the Keyspace corresponding to the right-hand side of JOIN.
lhs-expression: String or expression representing the Keyspace corresponding to the left-hand side of JOIN.
lhs-expression-key: String or expression representing the attribute in rhs-expression referencing the primary key for lhs-expression.

Index JOIN Example 1: ON KEY ... FOR.

The following example counts the number of distinct "AA" airline routes for each airport after creating the following index (if not already created).

CREATE INDEX route_airlineid ON `travel-sample`(airlineid) WHERE type="route";

SELECT Count(DISTINCT route.sourceairport) AS DistinctAirports
FROM `travel-sample` airline
  JOIN `travel-sample` route
  ON KEY route.airlineid FOR airline
WHERE route.type = "route"
AND airline.type = "airline"
AND airline.iata = "AA";

This results in:

[
  {
    "DistinctAirports": 429
  }
]

UNNEST Clause

If a document or object contains a nested array, UNNEST conceptually performs a join of the nested array with its parent object. Each resulting joined object becomes an input to the query. Unnests can be chained.

Syntax

[ join-type ] UNNEST path [ [ AS ] alias ]

Arguments

join-type

[Optional; default is INNER]

INNER: For each result object produced, both the left-hand and right-hand source objects must be non-MISSING and non-NULL.
LEFT or LEFT OUTER: A left-outer unnest is performed, and at least one result object is produced for each left source object.

For each joined object produced, only the left-hand source objects must be non-MISSING and non-NULL.

path

[Required] The first path element after each UNNEST must reference some preceding path.

alias (optionally, AS alias)

[Required] To assign a name for the unnested item. For details, see AS Keyword.

Return Values

If the right-hand source object is NULL, MISSING, empty, or a non-array value, then the result object’s right-side value is MISSING (omitted).

Example 1: UNNEST an array to select an item.

In the travel-sample keyspace, flatten the schedule array to get a list of the flights on Monday (1).

SELECT sched FROM `travel-sample` UNNEST schedule sched
WHERE  sched.day = 1
LIMIT 3;

This results in:

[
  {
    "sched": {
      "day": 1,
      "flight": "AF356",
      "utc": "12:40:00"
    }
  },
  {
    "sched": {
      "day": 1,
      "flight": "AF480",
      "utc": "08:58:00"
    }
  },
  {
    "sched": {
      "day": 1,
      "flight": "AF250",
      "utc": "12:59:00"
    }
  }
]

Another way to get similar results is by using a Collection Operator to find array items that meet our criteria:

SELECT ARRAY item FOR item IN schedule WHEN item.day = 1 END AS Monday_flights
FROM `travel-sample`
WHERE type = "route"
AND ANY item IN schedule SATISFIES item.day = 1 END
LIMIT 3;

However, without the UNNEST clause, the unflattened list results in 3 sets of flights instead of only 3 individual flights:

[
  {
    "Monday_flights": [
      {
        "day": 1,
        "flight": "AF356",
        "utc": "12:40:00"
      },
      {
        "day": 1,
        "flight": "AF480",
        "utc": "08:58:00"
      },
      {
        "day": 1,
        "flight": "AF250",
        "utc": "12:59:00"
      },
      {
        "day": 1,
        "flight": "AF130",
        "utc": "04:45:00"
      }
    ]
  },
  {
    "Monday_flights": [
      {
        "day": 1,
        "flight": "AF517",
        "utc": "13:36:00"
      },
      {
        "day": 1,
        "flight": "AF279",
        "utc": "21:35:00"
      },
      {
        "day": 1,
        "flight": "AF753",
        "utc": "00:54:00"
      },
      {
        "day": 1,
        "flight": "AF079",
        "utc": "15:29:00"
      },
      {
        "day": 1,
        "flight": "AF756",
        "utc": "06:16:00"
      }
    ]
  },
  {
    "Monday_flights": [
      {
        "day": 1,
        "flight": "AF975",
        "utc": "11:23:00"
      },
      {
        "day": 1,
        "flight": "AF225",
        "utc": "16:05:00"
      }
    ]
  }
]

Example 2: Use UNNEST to collect items from one array to use in another query.

In this example, the UNNEST clause iterates over the reviews array and collects the author names of the reviewers who rated the rooms less than a 2 to be contacted for ways to improve. This collection of objects can be used as input for other query operations.

SELECT RAW reviews.author
FROM `travel-sample`
UNNEST reviews
WHERE `travel-sample`.type = "hotel"
AND reviews.ratings.Rooms < 2
LIMIT 4;

This results in:

[
  "Kayli Cronin",
  "Shanelle Streich",
  "Catharine Funk",
  "Tyson Beatty"
]

NEST Clause

Nesting is conceptually the inverse of unnesting. Nesting performs a join across two keyspaces. But instead of producing a cross-product of the left and right inputs, a single result is produced for each left input, while the corresponding right inputs are collected into an array and nested as a single array-valued field in the result object.

Syntax

[ join-type ] NEST from-path [ [ AS ] alias ] on-keys-clause

Arguments

join-type

[Optional; default is INNER]

INNER: For each result object produced, both the left-hand and right-hand source objects must be non-MISSING and non-NULL.
LEFT or LEFT OUTER: A left-outer unnest is performed, and at least one result object is produced for each left source object.

For each joined object produced, only the left-hand source objects must be non-MISSING and non-NULL.

path

[Required] The first path element after each UNNEST must reference some preceding path.

alias (optionally, AS alias)

[Required] To assign a name for the unnested item. For details, see AS Keyword.

on-keys-clause

[Required] String or expression representing the primary keys of the documents for the second keyspace.

The ON KEYS expression produces one or more document keys for the right-hand side document.

The ON KEYS expression can produce an array of document keys.

Return Values

If the right-hand source object is NULL, MISSING, empty, or a non-array value, then the result object’s right-side value is MISSING (omitted).

Nests can be chained with other NEST, JOIN, and UNNEST clauses. By default, an INNER NEST is performed. This means that for each result object produced, both the left and right source objects must be non-missing and non-null. The right-hand side result of NEST is always an array or MISSING. If there is no matching right source object, then the right source object is as follows:

If the ON KEYS expression evaluates to Then the right-side value is

If the `ON KEYS` expression evaluates to	Then the right-side value is
`MISSING`	`MISSING`
`NULL`	`MISSING`
an array	an empty array
a non-array value	an empty array

MISSING

NULL

MISSING

an array

an empty array

a non-array value

an empty array

NEST Example 1: Join two keyspaces producing an output for each left input.

Show 1 set of routes for 1 airline in the `travel-sample` keyspace.

SELECT *
FROM `travel-sample` route
  INNER NEST `travel-sample` airline
  ON KEYS route.airlineid
WHERE route.type = "route"
LIMIT 1;

This results in:

[
  {
    "airline": [
      {
        "callsign": "AIRFRANS",
        "country": "France",
        "iata": "AF",
        "icao": "AFR",
        "id": 137,
        "name": "Air France",
        "type": "airline"
      }
    ],
    "route": {
      "airline": "AF",
      "airlineid": "airline_137",
      "destinationairport": "MRS",
      "distance": 2881.617376098415,
      "equipment": "320",
      "id": 10000,
      "schedule": [
        {
          "day": 0,
          "flight": "AF198",
          "utc": "10:13:00"
        },
        {
          "day": 0,
          "flight": "AF547",
          "utc": "19:14:00"
        },
        {
          "day": 0,
          "flight": "AF943",
          "utc": "01:31:00"
        },
        {
          "day": 1,
          "flight": "AF356",
          "utc": "12:40:00"
        },
        {
          "day": 1,
          "flight": "AF480",
          "utc": "08:58:00"
        },
        {
          "day": 1,
          "flight": "AF250",
          "utc": "12:59:00"
        },
        {
          "day": 1,
          "flight": "AF130",
          "utc": "04:45:00"
        },
        {
          "day": 2,
          "flight": "AF997",
          "utc": "00:31:00"
        },
        {
          "day": 2,
          "flight": "AF223",
          "utc": "19:41:00"
        },
        {
          "day": 2,
          "flight": "AF890",
          "utc": "15:14:00"
        },
        {
          "day": 2,
          "flight": "AF399",
          "utc": "00:30:00"
        },
        {
          "day": 2,
          "flight": "AF328",
          "utc": "16:18:00"
        },
        {
          "day": 3,
          "flight": "AF074",
          "utc": "23:50:00"
        },
        {
          "day": 3,
          "flight": "AF556",
          "utc": "11:33:00"
        },
        {
          "day": 4,
          "flight": "AF064",
          "utc": "13:23:00"
        },
        {
          "day": 4,
          "flight": "AF596",
          "utc": "12:09:00"
        },
        {
          "day": 4,
          "flight": "AF818",
          "utc": "08:02:00"
        },
        {
          "day": 5,
          "flight": "AF967",
          "utc": "11:33:00"
        },
        {
          "day": 5,
          "flight": "AF730",
          "utc": "19:42:00"
        },
        {
          "day": 6,
          "flight": "AF882",
          "utc": "17:07:00"
        },
        {
          "day": 6,
          "flight": "AF485",
          "utc": "17:03:00"
        },
        {
          "day": 6,
          "flight": "AF898",
          "utc": "10:01:00"
        },
        {
          "day": 6,
          "flight": "AF496",
          "utc": "07:00:00"
        }
      ],
      "sourceairport": "TLV",
      "stops": 0,
      "type": "route"
    }
  }
]

Arrays

If an array occurs along a path, you can use array subscripts to select one element.

Arrays Example 1a: List the arrays of public_likes.

SELECT public_likes
FROM `travel-sample`
WHERE type = "hotel"
LIMIT 4;

This results in:

[
  {
    "public_likes": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",                          / 3rd item
      "Vallie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow"
    ]
  },
  {
    "public_likes": []
  },
  {
    "public_likes": []
  },
  {
    "public_likes": [
      "Thomas Wilkinson",
      "Jesse Goyette",
      "Dallas Smitham",                            / 3rd item
      "Madilyn Kub",
      "Roxanne Murazik",
      "Kale Runte",
      "Ephraim Hane"
    ]
  }
]

Arrays Example 1b: List the third item of each public_likes array.

Remember that array counting starts with 0, so 2 points to the third item.

SELECT public_likes[2]
FROM `travel-sample`
WHERE type = "hotel"
LIMIT 4;

[
  {
    "$1": "Jaeden McKenzie"
  },
  {},
  {},
  {
    "$1": "Dallas Smitham"
  }
]

AS Keyword

Like SQL, N1QL allows renaming fields using the AS keyword. However, N1QL also allows reshaping the data, which has no analog in SQL. To do this, you embed the attributes of the statement in the desired result object shape.

Aliases

Aliases in the FROM clause create new names that can be referred to anywhere in the query. 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.