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 thefrom-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
orUSE INDEX
clauses are not allowed. -
When using a
JOIN
clause,NEST
clause, orUNNEST
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
orLEFT 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
orLEFT 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
orMISSING
, then the joined object’s right-hand side value is alsoNULL
orMISSING
(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 theON KEY
expressionroute.airlineid
to accessroute
documents usingairlineid
(which are produced on the LHS). -
The
ON KEY route.airlineid FOR airline
enables N1QL to use the indexroute.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
orLEFT 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 forlhs-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
orLEFT 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 isMISSING
(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
orLEFT 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 isMISSING
(omitted).Nests can be chained with other
NEST
,JOIN
, andUNNEST
clauses. By default, anINNER 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 ofNEST
is always an array orMISSING
. If there is no matching right source object, then the right source object is as follows:If the ON KEYS
expression evaluates toThen the right-side value is MISSING
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.