NEST Clause
- Capella Operational
- reference
The NEST
clause creates an input object by producing a single result of nesting keyspaces.
Purpose
The NEST
clause is used within the FROM clause.
It enables you to create an input object by producing a single result of nesting keyspaces via ANSI NEST, Lookup NEST, or Index NEST.
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
nest-clause ::= ansi-nest-clause | lookup-nest-clause | index-nest-clause
ansi-nest-clause | |
lookup-nest-clause | |
index-nest-clause |
Left-Hand Side
The NEST
clause cannot be the first term within the FROM
clause; it must be preceded by another FROM term.
The term immediately preceding the NEST
clause represents the left-hand side of the NEST
clause.
You can chain the NEST
clause with any of the other permitted FROM terms, including another NEST
clause.
For more information, see the page on the FROM clause.
There are restrictions on what types of FROM terms may be chained and in what order — see the descriptions on this page for more details.
The types of FROM term that may be used as the left-hand side of the NEST
clause are summarized in the following table.
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Type | Example |
---|---|
|
|
|
|
|
|
|
ANSI NEST Clause
ANSI JOIN and ANSI NEST clauses have much more flexible functionality than their earlier INDEX and LOOKUP equivalents. Since these are standard compliant and more flexible, we recommend you to use ANSI JOIN and ANSI NEST exclusively, where possible. |
ANSI NEST supports more nest types than Lookup NEST or Index NEXT. ANSI NEST can nest arbitrary fields of the documents, and can be chained together.
The key difference between ANSI NEST and other supported NEST types is the replacement of the ON KEYS
or ON KEY … FOR
clauses with a simple ON
clause.
The ON KEYS
or ON KEY … FOR
clauses dictate that those nests can only be done on a document key (primary key for a document).
The ON
clause can contain any expression, and thus it opens up many more nest possibilities.
Syntax
ansi-nest-clause ::= ansi-nest-type? 'NEST' 'LATERAL'? ansi-nest-rhs ansi-nest-predicate
ansi-nest-type | |
ansi-nest-lateral | |
ansi-nest-rhs | |
ansi-nest-predicate |
Nest Type
ansi-nest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
This clause represents the type of ANSI nest.
INNER
-
For each nested object produced, both the left-hand and right-hand source objects must be non-MISSING and non-NULL.
LEFT [OUTER]
-
[Query Service interprets
LEFT
asLEFT OUTER
]For each nested object produced, only the left-hand source objects must be non-MISSING and non-NULL.
This clause is optional.
If omitted, the default is INNER
.
LATERAL Nest
When an expression on the right-hand side of an ANSI nest references a keyspace that is already specified in the same FROM clause, the expression is said to be correlated. In relational databases, a join which contains correlated expressions is referred to as a lateral join. In SQL++, lateral correlations are detected automatically, and there is no need to specify that a nest or join is lateral.
In clusters using Couchbase Server 7.6 and later, you can use the LATERAL keyword as a visual reminder that a nest contains correlated expressions. The LATERAL keyword is not required — the keyword is included solely for compatibility with queries from relational databases.
If you use the LATERAL keyword in a nest that has no lateral correlation, the keyword is ignored.
INNER NEST and LEFT OUTER NEST support the optional LATERAL keyword in front of the right-hand side keyspace.
Nest Right-Hand Side
ansi-nest-rhs ::= keyspace-ref ( 'AS'? alias )?
keyspace-ref | |
alias |
Keyspace Reference
Keyspace reference or expression representing the right-hand side of the NEST clause. For details, see Keyspace Reference.
AS Alias
Assigns another name to the right-hand side of the NEST clause. For details, see AS Clause.
Assigning an alias to the keyspace reference is optional.
If you assign an alias to the keyspace reference, the AS
keyword may be omitted.
Nest Predicate
ansi-nest-predicate ::= 'ON' expr
expr
-
Boolean expression representing the nest condition between the left-hand side FROM term and the right-hand side Keyspace Reference. This expression may contain fields, constant expressions, or any complex SQL++ expression.
Limitations
-
Full OUTER nest and cross nest types are currently not supported.
-
No mixing of ANSI nest syntax with lookup or index nest syntax in the same FROM clause.
-
The right-hand-side of any nest must be a keyspace. Expressions, subqueries, or other join combinations cannot be on the right-hand-side of a nest.
-
A nest can only be executed when appropriate index exists on the inner side of the ANSI nest.
-
Adaptive indexes are not considered when selecting indexes on inner side of the nest.
-
You may chain ANSI nests with comma-separated joins; however, the comma-separated joins must come after any JOIN, NEST, or UNNEST clauses.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
List only airports in Toulouse which have routes starting from them, and nest details of the routes.
SELECT *
FROM airport a
INNER NEST route r
ON a.faa = r.sourceairport
WHERE a.city = "Toulouse"
ORDER BY a.airportname;
[
{
"a": {
"airportname": "Blagnac",
"city": "Toulouse",
"country": "France",
"faa": "TLS",
"geo": {
"alt": 499,
"lat": 43.629075,
"lon": 1.363819
},
"icao": "LFBO",
"id": 1273,
"type": "airport",
"tz": "Europe/Paris"
},
"r": [
{
"airline": "AH",
"airlineid": "airline_794",
"destinationairport": "ALG",
"distance": 787.299015326995,
"equipment": "736",
"id": 10265,
// ...
},
{
"airline": "AH",
"airlineid": "airline_794",
"destinationairport": "ORN",
"distance": 906.1483088609814,
"equipment": "736",
"id": 10266,
// ...
]
}
]
This example is the same as Example 1, but it includes the optional LATERAL keyword.
SELECT *
FROM airport a
NEST LATERAL (
SELECT r1.* FROM route r1
WHERE a.faa = r1.sourceairport
) AS r
ON true
WHERE a.city = "Toulouse"
ORDER BY a.airportname;
[
{
"a": {
"id": 1273,
"type": "airport",
"airportname": "Blagnac",
"city": "Toulouse",
"country": "France",
"faa": "TLS",
"icao": "LFBO",
"tz": "Europe/Paris",
"geo": {
"lat": 43.629075,
"lon": 1.363819,
"alt": 499
}
},
"r": [
{
"airline": "AH",
"airlineid": "airline_794",
"destinationairport": "ALG",
"distance": 787.299015326995,
"equipment": "736",
"id": 10265,
// ...
List all airports in Toulouse, and nest details of any routes that start from each airport.
SELECT *
FROM airport a
LEFT NEST route r
ON a.faa = r.sourceairport
WHERE a.city = "Toulouse"
ORDER BY a.airportname;
[
{
"a": {
"airportname": "Blagnac",
"city": "Toulouse",
"country": "France",
"faa": "TLS",
"geo": {
"alt": 499,
"lat": 43.629075,
"lon": 1.363819
},
"icao": "LFBO",
"id": 1273,
"type": "airport",
"tz": "Europe/Paris"
},
"r": [
{
"airline": "AH",
"airlineid": "airline_794",
"destinationairport": "ALG",
"distance": 787.299015326995,
"equipment": "736",
"id": 10265,
// ...
}
]
},
{
"a": {
"airportname": "Francazal",
"city": "Toulouse",
"country": "France",
"faa": null,
"geo": {
"alt": 535,
"lat": 43.545555,
"lon": 1.3675
},
"icao": "LFBF",
"id": 1266,
"type": "airport",
"tz": "Europe/Paris"
},
"r": [] (1)
},
{
"a": {
"airportname": "Lasbordes",
"city": "Toulouse",
"country": "France",
"faa": null,
"geo": {
"alt": 459,
"lat": 43.586113,
"lon": 1.499167
},
"icao": "LFCL",
"id": 1286,
"type": "airport",
"tz": "Europe/Paris"
},
"r": []
}
]
1 | The LEFT OUTER NEST lists all the left-side results, even if there are no matching right-side documents, as indicated by the results in which the fields from the route keyspace are null or missing. |
Lookup 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
lookup-nest-clause ::= lookup-nest-type? 'NEST' lookup-nest-rhs lookup-nest-predicate
lookup-nest-type | |
lookup-nest-rhs | |
lookup-nest-predicate |
Nest Type
lookup-nest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
This clause represents the type of lookup nest.
INNER
-
For each result object produced, both the left-hand and right-hand source objects must be non-
MISSING
and non-NULL
. LEFT [OUTER]
-
[Query Service interprets
LEFT
asLEFT 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
.
This clause is optional.
If omitted, the default is INNER
.
Nest Right-Hand Side
lookup-nest-rhs ::= keyspace-ref ( 'AS'? alias )?
keyspace-ref | |
alias |
Keyspace Reference
Keyspace reference for the right-hand side of the lookup nest. For details, see Keyspace Reference.
AS Alias
Assigns another name to the right-hand side of the lookup nest. For details, see AS Clause.
Assigning an alias to the keyspace reference is optional.
If you assign an alias to the keyspace reference, the AS
keyword may be omitted.
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 |
---|---|
|
|
|
|
an array |
an empty array |
a non-array value |
an empty array |
Limitations
Lookup nests can be chained with other lookup joins or nests and index joins or nests, but they cannot be mixed with ANSI joins, ANSI nests, or comma-separated joins.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
Show one set of routes for one airline in the airline
keyspace.
SELECT *
FROM route
INNER NEST airline
ON KEYS route.airlineid
LIMIT 1;
[
{
"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": [
// ...
],
"sourceairport": "TLV",
"stops": 0,
"type": "route"
}
}
]
Index NEST Clause
Index NESTs allow you to flip the direction of a Lookup NEST clause. Index NESTs can be used efficiently when Lookup NESTs cannot efficiently nest left-hand side documents with right-to-left nests, and your situation cannot be flipped because your predicate needs to be on the left-hand side, such as Example 4 above where airline documents have no reference to route documents.
For index nests, the syntax uses ON KEY (singular) instead of ON KEYS (plural).
This is because an Index NEST’s ON KEY expression must produce a scalar value; whereas a Lookup NEST’s ON KEYS expression can produce either a scalar or an array value.
|
Syntax
index-nest-clause ::= index-nest-type? 'NEST' index-nest-rhs index-nest-predicate
index-nest-type | |
index-nest-rhs | |
index-nest-predicate |
Nest Type
index-nest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
This clause represents the type of index nest.
INNER
-
For each nested object produced, both the left-hand and right-hand source objects must be non-MISSING and non-NULL.
LEFT [OUTER]
-
[Query Service interprets
LEFT
asLEFT OUTER
]For each nested object produced, only the left-hand source objects must be non-MISSING and non-NULL.
This clause is optional.
If omitted, the default is INNER
.
Nest Right-Hand Side
index-nest-rhs ::= keyspace-ref ( 'AS'? alias )?
keyspace-ref | |
alias |
Keyspace Reference
Keyspace reference or expression representing the right-hand side of the NEST clause. For details, see Keyspace Reference.
AS Alias
Assigns another name to the right-hand side of the NEST clause. For details, see AS Clause.
Assigning an alias to the keyspace reference is optional.
If you assign an alias to the keyspace reference, the AS
keyword may be omitted.
Nest Predicate
index-nest-predicate ::= 'ON' 'KEY' expr 'FOR' alias
expr
-
Expression in the form
rhs-expression.lhs-expression-key
:rhs-expression
-
Keyspace reference for the right-hand side of the index nest.
lhs-expression-key
-
String or expression representing the attribute in
rhs-expression
and referencing the document key foralias
.
alias
-
Keyspace reference for the left-hand side of the index nest.
Limitations
Index nests can be chained with other index joins or nests and lookup joins or nests, but they cannot be mixed with ANSI joins, ANSI nests, or comma-separated joins.
Examples
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
This example nests the airline routes for each airline after creating the following index. (Note that the index will not match if it contains a WHERE clause.)
CREATE INDEX route_airlineid ON route(airlineid);
SELECT *
FROM airline aline
INNER NEST route rte
ON KEY rte.airlineid FOR aline
LIMIT 1;
[
{
"aline": {
"callsign": "MILE-AIR",
"country": "United States",
"iata": "Q5",
"icao": "MLA",
"id": 10,
"name": "40-Mile Air",
"type": "airline"
},
"rte": [
{
"airline": "Q5",
"airlineid": "airline_10",
"destinationairport": "FAI",
"distance": 118.20183585107631,
"equipment": "CNA",
"id": 46587,
"schedule": [
// ...
],
"sourceairport": "HKB",
"stops": 0,
"type": "route"
},
{
"airline": "Q5",
"airlineid": "airline_10",
"destinationairport": "HKB",
"distance": 118.20183585107631,
"equipment": "CNA",
"id": 46586,
"schedule": [
// ...
],
"sourceairport": "FAI",
"stops": 0,
"type": "route"
}
]
}
]
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 NEST 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
expressionrte.airlineid
to accessroute
documents usingairlineid
(which are produced on the left-hand side). -
The
ON KEY rte.airlineid FOR aline
enables SQL++ to use the indexroute_airlineid
. -
Create any optional index, such as
route_airline
, that can be used onairline
(left-hand side).
Appendix: Summary of NEST Types
To try the examples in this section, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Query Context.
ANSI
Left-Hand Side (lhs) |
Any field or expression that produces a value that will be matched on the right-hand side. |
Right-Hand Side (rhs) |
Anything that can have a proper index on the join expression. |
Syntax |
lhs-expr NEST rhs-keyspace ON any nest condition |
Example |
|
Lookup
Left-Hand Side (lhs) |
Must produce a Document Key for the right-hand side. |
Right-Hand Side (rhs) |
Must have a Document Key. |
Syntax |
lhs-expr NEST rhs-keyspace ON KEYS lhs-expr.foreign_key |
Example |
|
Index
Left-Hand Side (lhs) |
Must produce a key for the right-hand side index. |
Right-Hand Side (rhs) |
Must have a proper index on the field or expression that maps to the Document Key of the left-hand side. |
Syntax |
lhs-keyspace NEST rhs-keyspace ON KEY rhs-kspace.idx_key FOR lhs-keyspace |
Example |
|