FROM clause

The FROM clause specifies the keyspaces and JOIN operations on them.

(Introduced in Couchbase Server 4.0)

Purpose

In a SELECT query or subquery, the FROM clause specifies one or more of the following:

  • Keyspaces

  • Subqueries (such as derived tables)

  • JOIN clauses

  • JOIN conditions

  • Expressions (nested collections, CURL(), or other expressions)

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 term 
FROM from-keyspace [ [ AS ] alias1 ] [ USE KEYS use-clause ]
                    | "(" SELECT ")" [ [ AS ] alias2 ]
                    | expr [ [ AS ] alias3 ]
                    | from-term ( join-clause | nest-clause | unnest-clause )
Table 1. Arguments
Argument / Clause Description

from-keyspace

. . . AS alias

. . . USE KEYS use-clause

Identifier that represents the keyspace for the query, such as FROM `travel-sample`

To assign a name to a keyspace or expression.

To specify one or more document keys.

( select-expr )

To specify a N1QL SELECT subquery

expr

A N1QL expression generating JSON documents or objects.

from-term

. . . JOIN join-clause

. . . NEST nest-clause

. . . UNNEST unnest-clause

A N1QL expression that defines the input object(s) for the query, which can be either a keyspace identifier, generic expression, or subquery along with one or more JOIN, NEST, or UNNEST clause.

Create an input object by combining two or more source objects via ANSI JOIN, Lookup JOIN, or Index JOIN.

Create an input object by producing a single result of nesting keyspaces via ANSI NEST, Lookup NEST, or Index NEST.

Create an input object by flattening an array in the parent document.

from-keyspace

You can specify a keyspace to query from, either a specific bucket or a constant expression. If the from-keyspace clause is used, then there must be a from-keyspace-name specified.

The simplest type of from-keyspace clause specifies a single bucket (i.e., SELECT * FROM `travel-sample`).

Keyspace identifier is the name or identifier of an independent place for a data source of one or more documents. Such keyspaces are not dependent on any of the Variable in Scope of a Subquery.

from-keyspace Example: 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"
  }
]

N1QL Expressions in a FROM Clause

Couchbase Server version 4.6.2 added support for generic expressions in the from-term clause; 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.

  • 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.

    1. 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 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.

    2. Variable N1QL Expression

    This includes expressions that refer to any variables in scope for the query, for example:

    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.

    3. Subquery and Subquery Expressions

    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;

    For more details and examples, see Subqueries and ( select-expr ).

AS Alias

To use a shorter or clearer name anywhere in the query, like SQL, N1QL allows renaming fields by using the AS keyword to assign an alias to a keyspace or field in the FROM clause.

Syntax

[AS] alias

Arguments

AS

[Optional] Reserved word denoting the next word is an alias of the previous term.

alias

[Required if AS is used] String to assign a name to a keyspace, such as the following equivalent FROM clauses 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)`

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.

USE KEYS Clause

You can refer to a document’s unique document key by using the USE KEYS clause. Only documents having those document keys will be included as inputs to a query.

Syntax

use keys clause 
USE [ PRIMARY ] KEYS expr
Arguments
PRIMARY

[Optional] USE KEYS and USE PRIMARY KEYS are synonyms.

expr

String of a document key or an array of comma-separated document keys.

USE KEYS Example 1: Select a single document by its document key.

SELECT *
FROM `travel-sample`
USE KEYS "airport_1254";

Results:

[
  {
    "travel-sample": {
      "airportname": "Calais Dunkerque",
      "city": "Calais",
      "country": "France",
      "faa": "CQF",
      "geo": {
        "alt": 12,
        "lat": 50.962097,
        "lon": 1.954764
      },
      "icao": "LFAC",
      "id": 1254,
      "type": "airport",
      "tz": "Europe/Paris"
    }
  }
]

USE KEYS Example 2: Select multiple documents by their document keys.

SELECT *
FROM `travel-sample`
USE KEYS ["airport_1254","airport_1255"];

Results:

[
  {
    "travel-sample": {
      "airportname": "Calais Dunkerque",
      "city": "Calais",
      "country": "France",
      "faa": "CQF",
      "geo": {
        "alt": 12,
        "lat": 50.962097,
        "lon": 1.954764
      },
      "icao": "LFAC",
      "id": 1254,
      "type": "airport",
      "tz": "Europe/Paris"
    }
  },
  {
    "travel-sample": {
      "airportname": "Peronne St Quentin",
      "city": "Peronne",
      "country": "France",
      "faa": null,
      "geo": {
        "alt": 295,
        "lat": 49.868547,
        "lon": 3.029578
      },
      "icao": "LFAG",
      "id": 1255,
      "type": "airport",
      "tz": "Europe/Paris"
    }
  }
]

( select-expr )

Use parenthesis to specify a N1QL SELECT expression of input objects.

Arguments

select-expr

[Required] The N1QL SELECT query of input objects.

Example 1: 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"
  }
]

For more details and examples, see SELECT Clause.

from-term

The from-term defines the input object(s) for the query, and it can be one of the following types:

Type Example

keyspace identifier

`travel-sample`

generic expression

20+10 AS Total

subquery

SELECT t1.country, ARRAY_AGG(t1.city), SUM(t1.city_cnt) AS apnum

FROM (SELECT city, city_cnt, ARRAY_AGG(airportname) AS apnames, country

FROM `travel-sample`

WHERE type = "airport"

GROUP BY city, country

LETTING city_cnt = COUNT(city) ) AS t1

WHERE t1.city_cnt > 5;

previous join, nest, or unnest

SELECT *

FROM `travel-sample` AS rte

JOIN `travel-sample` AS aln

ON rte.airlineid = META(aln).id

NEST `travel-sample` AS lmk

ON aln.landmarkid = META(lmk).id;

For more details with examples, click the above links.

Couchbase Server version 4.6.2 adds support for generic expression in the from-term. Prior Couchbase Server versions support only the other two types.

ANSI JOIN Clause

(Introduced in Couchbase Server Enterprise Edition 5.5)

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.

Purpose

To be closer to standard SQL syntax, ANSI JOIN can join arbitrary fields of the documents and can be chained together.

The following table lists the JOIN types currently supported.

Join Type Remarks Example

[INNER] JOIN ... ON

INNER JOIN and LEFT OUTER JOIN can be mixed in any number and/or order.

SELECT *

FROM `travel-sample` r

JOIN `travel-sample` a

ON r.airlineid = META(a).id

WHERE a.country = "France"

LEFT [OUTER] JOIN ... ON

SELECT *

FROM `travel-sample` r

LEFT JOIN `travel-sample` a

ON r.airlineid = META(a).id

WHERE r.sourceairport = "SFO"

RIGHT [OUTER] JOIN ... ON

RIGHT OUTER JOIN can only be the first join specified in a FROM clause.

SELECT *

FROM `travel-sample` r

RIGHT JOIN `travel-sample` a

ON r.airlineid = META(a).id

WHERE r.sourceairport = "SFO"

Syntax

FROM ansi join RR clause 5.5 
lhs-expr [join-type] JOIN rhs-expr ON join-clause

Arguments

lhs-expr

[Required] Keyspace reference or expression representing the left-hand side of the join clause.

join-type

[Optional. Default is INNER] String representing the type of join.

INNER

[Optional. Default is INNER]

For each joined object produced, both the left-hand side and right-hand side source objects of the ON clause must be non-MISSING and non-NULL.

LEFT [OUTER]

[Optional. Query Service interprets LEFT as LEFT OUTER]

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

RIGHT [OUTER]

[Optional. Query Service interprets RIGHT as RIGHT OUTER]

For each joined object produced, only the right-hand source objects of the ON clause must be non-MISSING and non-NULL

JOIN rhs-expr

[Required] Keyspace reference or expression representing the right-hand side of the join clause.

ON join-clause

[Required] Boolean expression representing the join condition between the left-hand side expression and the right-hand side expression, which can be fields, constant expressions or any complex N1QL expression.

ANSI Join Example 1: Inner Join.

List the source airports and airlines that fly into SFO, where only the non-null route documents join with matching airline documents.

SELECT route.airlineid, airline.name, route.sourceairport, route.destinationairport
FROM `travel-sample` route
INNER JOIN `travel-sample` airline
ON route.airlineid = META(airline).id
WHERE route.type = "route"
AND route.destinationairport = "SFO"
ORDER BY route.sourceairport;

Results:

[
  {
    "airlineid": "airline_5209",
    "destinationairport": "SFO",
    "name": "United Airlines",
    "sourceairport": "ABQ"
  },
  {
    "airlineid": "airline_5209",
    "destinationairport": "SFO",
    "name": "United Airlines",
    "sourceairport": "ACV"
  },
  {
    "airlineid": "airline_5209",
    "destinationairport": "SFO",
    "name": "United Airlines",
    "sourceairport": "AKL"
  },
...

ANSI Join Example 2: Left Outer Join of U.S. airports in the same city as a landmark.

List the airports and landmarks in the same city, ordered by the airports.

SELECT DISTINCT  MIN(aport.airportname) AS Airport__Name,
                 MIN(lmark.name) AS Landmark_Name,
                 MIN(aport.tz) AS Landmark_Time
FROM `travel-sample` aport
LEFT JOIN `travel-sample` lmark
  ON aport.city = lmark.city
  AND lmark.country = "United States"
  AND lmark.type = "landmark"
WHERE aport.type = "airport"
GROUP BY lmark.name
ORDER BY lmark.name;

Results:

[
  {
    "Airport__Name": "San Francisco Intl",
    "Landmark_Name": ""Hippie Temptation" house",
    "Landmark_Time": "America/Los_Angeles"
  },
  {
    "Airport__Name": "Los Angeles Intl",
    "Landmark_Name": "101 Coffee Shop",
    "Landmark_Time": "America/Los_Angeles"
  },
  {
    "Airport__Name": "San Francisco Intl",
    "Landmark_Name": "1015",
    "Landmark_Time": "America/Los_Angeles"
  },
  {
    "Airport__Name": "San Francisco Intl",
    "Landmark_Name": "1235 Masonic Ave",
    "Landmark_Time": "America/Los_Angeles"
  },
...

ANSI Join Example 3: RIGHT OUTER JOIN of Example #2.

List the airports and landmarks in the same city, ordered by the landmarks.

The LEFT OUTER JOIN will list all left-side results regardless of matching right-side documents; while the RIGHT OUTER JOIN will list all right-side results regardless of matching left-side documents. 
SELECT DISTINCT  MIN(aport.airportname) AS Airport_Name,
                 MIN(lmark.name) AS Landmark_Name,
                 MIN(aport.tz) AS Landmark_Time
FROM `travel-sample` aport
RIGHT JOIN `travel-sample` lmark
  ON aport.city = lmark.city
  AND aport.type = "airport"
  AND aport.country = "United States"
WHERE lmark.type = "landmark"
GROUP BY lmark.name
ORDER BY lmark.name;

Results:

[
  {
    "Airport_Name": "San Francisco Intl",
    "Landmark_Name": ""Hippie Temptation" house",
    "Landmark_Time": "America/Los_Angeles"
  },
  {
    "Airport_Name": "London-Corbin Airport-MaGee Field",
    "Landmark_Name": "02 Shepherd's Bush Empire",
    "Landmark_Time": "America/New_York"
  },
  {
    "Airport_Name": "Los Angeles Intl",
    "Landmark_Name": "101 Coffee Shop",
    "Landmark_Time": "America/Los_Angeles"
  },
  {
    "Airport_Name": "San Francisco Intl",
    "Landmark_Name": "1015",
    "Landmark_Time": "America/Los_Angeles"
  },
...

ANSI Join Example #4: In the `beer-sample` bucket, use an ANSI JOIN to list the beer names and breweries that are in the state Wisconsin (WI). First, create an index with beer.brewry_id as the leading key.

CREATE INDEX beer_brewery ON `beer-sample` (brewery_id)
WHERE type = "beer"

SELECT META(brewery).id bid, META(beer).id, brewery.name brewery_name,
       beer.name beer_name
FROM `beer-sample` brewery
JOIN `beer-sample` beer
  ON beer.brewery_id = LOWER(REPLACE(brewery.name, " ", "_"))
WHERE beer.type = "beer"
  AND brewery.type = "brewery"
  AND brewery.state = "WI";

Results:

[
  {
    "beer_name": "Dank",
    "bid": "oso",
    "brewery_name": "Oso",
    "id": "oso-dank"
  }
  ]

Visual Explain Plan: FROM AnsiJoin Ex4 BeerVisual1

If you add name as the second index key to the beer_brewery index:

CREATE INDEX beer_brewery_name ON `beer-sample` (brewery_id, name)
WHERE type = "beer"

... then you will get covering index scan, as shown in the Visual Explain Plan:

FROM AnsiJoin Ex4 BeerVisual2

Limitations

The following Join types are currently not supported:

  • RIGHT OUTER JOIN is only supported when it’s the only join in the query; or in a chain of joins, the RIGHT OUTER JOIN must be the first join in the chain.

  • No mixing of new ANSI Join syntax with Lookup/Index Join syntax in the same FROM clause.

  • The right-hand-side of any join must be a keyspace. Expressions, subqueries, or other join combinations cannot be on the right-hand-side of a join.

  • A join can only be executed when appropriate index exists on the inner side of the join.

  • Adaptive indexes are not considered when selecting indexes on inner side of the join.

ANSI JOIN Hints (HASH & NL)

(Introduced in Couchbase Server Enterprise Edition 5.5)

Couchbase Server Enterprise Edition supports two join methods for performing ANSI Join: nested-loop join and hash join. The default join method is nested-loop join. Two corresponding join hints are introduced: USE HASH and USE NL.

Hash join is only considered when the USE HASH hint is specified, and it requires at least one equality predicate between the left-hand side and right-hand side. In such cases, if a hash join is chosen successfully, then that’ll be the join method used for this join. If the hash join cannot be generated, then the planner will further consider nested-loop join and will either generate a nested-loop join or return an error for the join.

If no join hint is specified or USE NL hint is specified, then nested-loop join is considered.

For Community Edition (CE), any specified USE HASH hint will be silently ignored and only nested-loop join is considered by the planner.

USE HASH hint

The USE HASH hint is similar to the existing USE INDEX or USE KEYS hint in that the USE HASH hint can be specified after a keyspace reference in an ANSI Join specification. There are two versions of the USE HASH hint that indicate whether the keyspace is to be used as:

  • The build side of the hash join — USE HASH(build)

  • The probe side of the hash join — USE HASH(probe)

A hash join has two sides: a BUILD and a PROBE. The BUILD side of the join will be used to create an in-memory hash table. The PROBE side will use that table to find matches and perform the join. Typically, this means you want the BUILD side to be used on the smaller of the two sets. However, you can only supply one hash hint, and only to the right side of the join. So if you specify BUILD on the right side, then you are implicitly using PROBE on the left side (and vice versa).

USE HASH Example 1: PROBE

The keyspace aline is to be joined (with rte) using hash join, and aline is used as the probe side of the hash join.

SELECT COUNT(1) AS Total_Count
FROM `travel-sample` rte
INNER JOIN `travel-sample` aline
USE HASH (PROBE)
ON (rte.airlineid = META(aline).id)
WHERE rte.type = "route";

Results:

[
  {
    "Total_Count": 17629
  }
]

USE HASH Example 2: BUILD

This is effectively the same query as the previous example, except the two keyspaces are switched, and here the USE HASH(BUILD) hint is used, indicating the hash join should use rte as the build side.

SELECT COUNT(1) AS Total_Count
FROM `travel-sample` aline
INNER JOIN `travel-sample` rte
USE HASH (BUILD)
ON (rte.airlineid = META(aline).id)
WHERE rte.type = "route";

Results:

[
  {
    "Total_Count": 17629
  }
]

USE NL hint

This join hint instructs the planner to use nested-loop join (NL join) for the join being considered. Since nested-loop join is the default path, the USE NL hint is not required.

USE NL Example:

SELECT COUNT(1) AS Total_Count
FROM `travel-sample` rte
INNER JOIN `travel-sample` aline
USE NL
ON (rte.airlineid = META(aline).id)
WHERE rte.type = "route";
The join hint for the first join should be specified on the 2nd keyspace reference, and the join hint for the second join should be specified on the 3rd keyspace reference, etc. If a join hint is specified on the first keyspace, an error is returned.

Multiple hints

You can use only one join hint (USE HASH or USE NL) together with only one other hint (USE INDEX or USE KEYS) for a total of two hints. The order of the two hints doesn’t matter.

When multiple hints are being specified, use only one USE keyword with one following the other, as in the following examples.

Multiple hint Example 1: USE INDEX with USE HASH.

SELECT COUNT(1) AS Total_Count
FROM `travel-sample` rte
INNER JOIN `travel-sample` aline
USE INDEX idx1 HASH (PROBE)
ON (rte.airlineid = META(aline).id)
WHERE rte.type = "route";

Multiple hint Example 2: USE HASH with USE KEYS.

SELECT COUNT(1) AS Total_Count
FROM `travel-sample` rte
INNER JOIN `travel-sample` aline
USE HASH (PROBE) KEYS ["airline_key1", "airline_key2", "airline_key3"]
ON (rte.airlineid = META(aline).id)
WHERE rte.type = "route";

When chosen, the hash join will always work; the restrictions are on any USE KEYS hint clause:

  • Must not depend on any previous keyspaces.

  • The expression must be constants, host variables, etc.

  • Must not contain any subqueries.

If the USE KEYS hint contains references to other keyspaces or subqueries, then the USE HASH hint will be ignored and nested-loop join will be used instead.

ANSI JOIN and Arrays

ANSI JOIN provides great flexibility since the ON clause of an ANSI JOIN can be any expression as long as it evaluates to TRUE or FALSE. Below are different join scenarios involving arrays and ways to handle each scenario.

These buckets and indexes will be used throughout this section’s array scenarios. As a convention, when a field name starts with a it is an array, so each bucket has two array fields and two regular fields. Also, both _idx1 indexes index each element of its array, while both _idx2 indexes use its entire array as the index key.

bucket b1 (a11, a12, c11, c12)

bucket b2 (a21, a22, c21, c22)

CREATE INDEX b1_idx1 ON b1 (c11, c12, DISTINCT a11)

CREATE INDEX b1_idx2 ON b1 (a12)

CREATE INDEX b2_idx1 ON b2 (c21, c22, DISTINCT a21)

CREATE INDEX b2_idx2 ON b2 (a22)

ANSI JOIN with no arrays

In this scenario, there is no involvement of arrays in the join. These are just straight-forward joins:

SELECT *
FROM b1
JOIN b2
  ON b1.c11 = b2.c21
  AND b2.c22 = 100
WHERE b1.c12 = 10;

Here the joins are using non-array fields of each keyspace.

The following case also falls in this scenario:

SELECT *
FROM b1
JOIN b2
  ON b1.c11 = b2.c21
  AND b2.c22 = 100
  AND ANY v IN b2.a21 SATISFIES v = 10 END
WHERE b1.c12 = 10;

In this example, although there is an ANY predicate on the right-hand side array b2.a21, the ANY predicate does not involve any joins, and thus, as far as the join is concerned, it is still a 1-to-1 join. Similarly:

SELECT *
FROM b1
JOIN b2
  ON b1.c11 = b2.c21
WHERE b1.c11 = 10
  AND b1.c12 = 100
  AND ANY v IN b1.a11 SATISFIES v = 20 END;

In this case the ANY predicate is on the left-hand side array b1.a11; however, similar to above, the ANY predicate does not involve any joins, and thus the join is still 1-to-1. We can even have ANY predicates on both sides:

SELECT *
FROM b1
JOIN b2
  ON b1.c11 = b2.c21
  AND b2.c22 = 100
  AND ANY v IN b2.a21 SATISFIES v = 10 END
WHERE b1.c11 = 10
  AND b1.c12 = 100
  AND ANY v IN b1.a11 SATISFIES v = 10 END;

Again, the ANY predicates do not involve any join, and the join is still 1-to-1.

ANSI JOIN with entire array as index key

As a special case, it is possible to perform ANSI JOIN on an entire array as a join key:

SELECT *
FROM b1
JOIN b2
  ON b1.a21 = b2.a22
WHERE b1.c11 = 10
  AND b1.c12 = 100;

In this case, the entire array must match each other for the join to work. For all practical purposes, the array here is treated as a scalar since there is no logic to iterate through elements of an array here. The entire array is used as an index key (b2_idx2) and as such, an entire array is used as an index span to probe the index. The join here can also be considered as 1-to-1.

ANSI JOIN involving right-hand-side arrays

In this scenario, the join involves an array on the right-hand side keyspace:

SELECT *
FROM b1
JOIN b2
  ON b2.c21 = 10
  AND b2.c22 = 100
  AND ANY v IN b2.a21 SATISFIES v = b1.c12 END
WHERE b1.c11 = 10;

In this case, the ANY predicate involves a join, and thus, effectively we are joining b1 with elements of the b2.a21 array. This now becomes a 1-to-many join. Note that we use an ANY clause for this scenario since it’s a natural extension of the existing support for array indexes; the only difference is for index span generation, we now can have a potential join expression. Array indexes can be used for join in this scenario.

ANSI JOIN involving left-hand-side arrays

This is a slightly more complex scenario, where the array reference is on the left-hand side of the join, and it’s a many-to-1 join. There are two alternative ways to handle the scenario where the array appears on the left-hand side of the join.

Alternative #1: use UNNEST

This alternative will flatten the left-hand side array first, before performing the join:

SELECT *
FROM b1 UNNEST b1.a12 AS ba1
JOIN b2
  ON ba1 = b2.c22
  AND b2.c21 = 10
WHERE b1.c11 = 10
  AND b1.c12 = 100;

The UNNEST operation is used to flatten the array, turning one left-hand side document into multiple documents; and then for each one of them, join with the right-hand side. This way, by the time join is being performed, it is a regular join, since the array is already flattened in the UNNEST step.

Alternative #2: use IN clause

This alternative uses the IN clause to handle the array:

SELECT *
FROM b1
JOIN b2
  ON b2.c22 IN b1.a12 AND b2.c21 = 10
WHERE b1.c11 = 10 AND b1.c12 = 100;

By using the IN clause, the right-hand side field value can match any of the elements of the left-hand side array. Conceptually, we are using each element of the left-hand side array to probe the right-hand side index.

Differences between the two alternatives

There is a semantical difference between the two alternatives. With UNNEST, we are first turning one left-hand side document into multiple documents and then performing the join. With IN-clause, there is still only one left-hand side document, which can then join with one or more right-hand side documents. Thus:

  • If the array contains duplicate values,

    • the UNNEST method treats each duplicate as an individual value and thus duplicated results will be returned;

    • the IN clause method will not duplicate the result.

  • If no duplicate values exists and we are performing inner join,

    • then the two alternatives will likely give the same result.

  • If outer join is performed, assuming there are N elements in the left-hand side array, and assuming there is at most one matching document from the right-hand side for each element of the array,

    • the UNNEST method will produce N result documents;

    • the IN clause method may produce < N result documents if some of the array elements do not have matching right-hand side documents.

ANSI JOIN with arrays on both sides

If the join involves arrays on both sides, then we can combine the approaches above, i.e., using ANY clause to handle the right-hand side array and either UNNEST or IN clause to handle the left-hand side array. For example:

SELECT *
FROM b1
UNNEST b1.a12 AS ba1
  JOIN b2
    ON ANY v IN b2.a21 SATISFIES v = ba1 END
    AND b2.c21 = 10
    AND b2.c22 = 100
WHERE b1.c11 = 10
  AND b1.c12 = 100;

or

SELECT *
FROM b1
JOIN b2
  ON ANY v IN b2.a21 SATISFIES v IN b1.a12 END
  AND b2.c21 = 10
  AND b2.c22 = 100
WHERE b1.c11 = 10
  AND b1.c12 = 100;

Lookup JOIN Clause

(Introduced in Couchbase Server 4.0)

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 the ON KEYS expression must produce a document key which is then used to retrieve documents from the right-hand side keyspace. Couchbase Server version 4.1 and earlier supported only lookup joins.

Syntax

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

[Optional; default is INNER]

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] Keyspace reference for right-hand side of lookup join. 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 right-hand side 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 can be chained with other lookup joins/nests or index joins/nests, but they cannot be mixed with an ANSI JOIN or ANSI NEST.

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;

Results:

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

Results:

[
  {
    "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"
      }
    ]
  }
]

Index JOIN Clause

(Introduced in Couchbase Server 4.0)

When Lookup JOINs cannot efficiently join left-hand side documents with right-to-left joins and your situation cannot be flipped because your predicate needs to be on the left-hand side (such as the above Lookup Example #1 where airline documents have no reference to route documents), then Index JOINs can be used efficiently without making a Cartesian product of all route documents. Index JOINs allow you to flip the direction of your join clause.

Consider the below query similar to the above Lookup Example #1 with route and airline documents where route.airlineid is the document 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 airline.icao = "SEA"
LIMIT 4;

This query gets a list of Seattle (SEA) flights, but getting SEA flights 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 SEA flights 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 airline_icao ON `travel-sample`(icao) WHERE type="airline";

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 airline.icao = "SEA";

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.airline that can be used on airline (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

FROM index join clause 
[ 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

Keyspace reference for right-hand side of an index join. For details, see Keyspaces.

AS alias

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

ON KEY rhs-expression.lhs-expression-key
rhs-expression

Keyspace reference for the right-hand side of the index join.

lhs-expression-key

String or expression representing the attribute in rhs-expression referencing the document key for lhs-expression.

FOR lhs-expression

Keyspace reference for the left-hand side of the index join.

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

Results:

[
  {
    "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 output of the query. Unnests can be chained.

Syntax

FROM unnest clause 4.0 RR 
[ join-type ] UNNEST path [ [ AS ] alias ]
Arguments
join-type

[Optional; default is INNER]

INNER

For each result object produced, the array object in the left-hand side keyspace must be non-empty.

LEFT or LEFT OUTER

A left-outer unnest is performed, and at least one result object is produced for each left source object.

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).

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

Results :

[
  {
    "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"
      }
    ]
  }
]

UNNEST 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. r is an element of the array generated by the UNNEST operation.

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

This results in:

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

ANSI NEST Clause

(Introduced in Couchbase Server Enterprise Edition 5.5)

ANSI NEST (and ANSI JOIN) clauses are much faster and have much more flexible functionality than their earlier INDEX and LOOKUP equivalents, so users are strongly recommended to use ANSI NEST (and ANSI JOIN) exclusively, where possible.

ANSI NEST supports more nest types than Couchbase Server version 4.0’s NEST was able. ANSI NEST can nest arbitrary fields of the documents and can be chained together.

The key difference between the currently supported nests and ANSI NEST support is the replacement of the current 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 that Couchbase did not previously support.

Syntax

FROM ansi nest RR clause 5.5

lhs-expr [nest-type] NEST rhs-expr ON nest-clause

Arguments

lhs-expr

[Required] Keyspace reference or expression representing the left-hand side of the nest clause.

nest-type

[Optional. Default is INNER] String representing the type of nest.

INNER

[Optional. Default is INNER]

For each nested object produced, both the left-hand and right-hand source objects must be non-MISSING and non-NULL.

LEFT [OUTER]

[Optional. Query Service interprets LEFT as LEFT OUTER]

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

NEST rhs-expr

[Required] Keyspace reference or expression representing the right-hand side of the nest clause.

ON nest-clause

[Required] Boolean expression representing the nest condition between the left-hand side expression and the right-hand side expression, which can be fields, constant expressions or any complex N1QL expression.

Limitations

The following nest types are currently not supported:

  • Full OUTER NEST

  • Cross NEST

  • No mixing of new ANSI NEST syntax with 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 (similar to current NEST support).

  • Adaptive indexes are not considered when selecting indexes on inner side of the nest

ANSI NEST Example 1: Inner ANSI NEST

List the airlines, their plane model (equipment), and number of stops for flights between San Francisco and Boston.

SELECT r.airline, r.equipment, r.stops
FROM `travel-sample` r
  NEST `travel-sample` a
  ON r.airlineid = META(a).id
WHERE r.sourceairport = "SFO"
AND r.destinationairport = "BOS";

Results:

[
  {
    "airline": "B6",
    "equipment": "320",
    "stops": 0
  },
  {
    "airline": "UA",
    "equipment": "752 753 738 739 319 320",
    "stops": 0
  },
  {
    "airline": "VX",
    "equipment": "320",
    "stops": 0
  }
]

Lookup NEST Clause

(Introduced in Couchbase Server 4.0)

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

FROM lookup nest 4.0 RR 
[ 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.

from-path

[Required] Keyspace reference for right-hand side of lookup nest. For details, see Keyspaces.

alias (optionally, AS alias)

[Required] To assign a name for the right-hand side keyspace. 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

MISSING

MISSING

NULL

MISSING

an array

an empty array

a non-array value

an empty array

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

Show one set of routes for one 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;

Results:

[
  {
    "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"
    }
  }
]

Index NEST Clause

(Introduced in Couchbase Server 4.0)

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 the above Lookup NEST Example #1 where airline documents have no reference to route documents), then Index NESTs can be used efficiently. Index NESTs allow you to flip the direction of your nest clause.

Index NEST Example 1: List four

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

SELECT *
FROM `travel-sample` rte
  INNER NEST `travel-sample` aline
  ON KEY rte.airlineid
  FOR rte
WHERE rte.type = "route"
LIMIT 4;

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 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.airline that can be used on airline (LHS).

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

Syntax

FROM index nest 5.1 rr 
[ nest-type ] NEST from-path [ [ AS ] alias ] ON KEY on-key-clause FOR for-clause

Arguments

nest-type

[Optional; default is LEFT INNER]

LEFT or LEFT INNER

For each nested object produced, both the left-hand and right-hand source objects must be non-MISSING and non-NULL.

LEFT OUTER

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

from-path

Keyspace reference for right-hand side of an index nest. For details, see Keyspaces.

AS alias

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

ON KEY 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 referencing the document key for lhs-expression.

FOR lhs-expression

Keyspace reference for the left-hand side of the index nest.

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

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_airline ON `travel-sample`(airlineid);

SELECT *
FROM `travel-sample` aline
  INNER NEST `travel-sample` rte
  ON KEY rte.airlineid
  FOR aline
WHERE aline.type = "airline"
LIMIT 1;

Results:

[
  {
    "aline": {
      "callsign": "MILE-AIR",
      "country": "United States",
      "iata": "Q5",
      "icao": "MLA",
      "id": 10,
      "name": "40-Mile Air",
      "type": "airline"
    },
    "route": [
      {
        "airline": "Q5",
        "airlineid": "airline_10",
        "destinationairport": "HKB",
        "distance": 118.20183585107631,
        "equipment": "CNA",
        "id": 46586,
        "schedule": [
          {
            "day": 0,
            "flight": "Q5188",
            "utc": "12:40:00"
          },
          {
            "day": 0,
            "flight": "Q5630",
            "utc": "21:53:00"
          },
          {
            "day": 0,
            "flight": "Q5530",
            "utc": "07:47:00"
          },
          {
            "day": 0,
            "flight": "Q5132",
            "utc": "01:10:00"
          },
          {
            "day": 0,
            "flight": "Q5746",
            "utc": "20:11:00"
          },
          {
            "day": 1,
            "flight": "Q5413",
            "utc": "08:07:00"
          },
          {
            "day": 2,
            "flight": "Q5263",
            "utc": "17:39:00"
          },
          {
            "day": 2,
            "flight": "Q5564",
            "utc": "01:55:00"
          },
          {
            "day": 2,
            "flight": "Q5970",
            "utc": "00:09:00"
          },
          {
            "day": 2,
            "flight": "Q5295",
            "utc": "21:24:00"
          },
          {
            "day": 2,
            "flight": "Q5051",
            "utc": "04:41:00"
          },
          {
            "day": 3,
            "flight": "Q5023",
            "utc": "00:16:00"
          },
          {
            "day": 3,
            "flight": "Q5554",
            "utc": "11:45:00"
          },
          {
            "day": 3,
            "flight": "Q5619",
            "utc": "22:22:00"
          },
          {
            "day": 4,
            "flight": "Q5279",
            "utc": "23:19:00"
          },
          {
            "day": 4,
            "flight": "Q5652",
            "utc": "13:35:00"
          },
          {
            "day": 4,
            "flight": "Q5631",
            "utc": "17:53:00"
          },
          {
            "day": 4,
            "flight": "Q5105",
            "utc": "21:54:00"
          },
          {
            "day": 5,
            "flight": "Q5559",
            "utc": "01:19:00"
          },
          {
            "day": 5,
            "flight": "Q5600",
            "utc": "17:36:00"
          },
          {
            "day": 6,
            "flight": "Q5854",
            "utc": "22:59:00"
          },
          {
            "day": 6,
            "flight": "Q5217",
            "utc": "11:58:00"
          },
          {
            "day": 6,
            "flight": "Q5756",
            "utc": "06:32:00"
          },
          {
            "day": 6,
            "flight": "Q5151",
            "utc": "15:14:00"
          }
        ],
        "sourceairport": "FAI",
        "stops": 0,
        "type": "route"
      },
      {
        "airline": "Q5",
        "airlineid": "airline_10",
        "destinationairport": "FAI",
        "distance": 118.20183585107631,
        "equipment": "CNA",
        "id": 46587,
        "schedule": [
          {
            "day": 0,
            "flight": "Q5492",
            "utc": "17:00:00"
          },
          {
            "day": 0,
            "flight": "Q5357",
            "utc": "09:44:00"
          },
          {
            "day": 0,
            "flight": "Q5873",
            "utc": "00:01:00"
          },
          {
            "day": 1,
            "flight": "Q5171",
            "utc": "00:59:00"
          },
          {
            "day": 1,
            "flight": "Q5047",
            "utc": "10:57:00"
          },
          {
            "day": 1,
            "flight": "Q5889",
            "utc": "14:51:00"
          },
          {
            "day": 1,
            "flight": "Q5272",
            "utc": "18:36:00"
          },
          {
            "day": 2,
            "flight": "Q5673",
            "utc": "21:30:00"
          },
          {
            "day": 3,
            "flight": "Q5381",
            "utc": "20:01:00"
          },
          {
            "day": 4,
            "flight": "Q5261",
            "utc": "18:37:00"
          },
          {
            "day": 5,
            "flight": "Q5755",
            "utc": "23:43:00"
          },
          {
            "day": 5,
            "flight": "Q5544",
            "utc": "16:04:00"
          },
          {
            "day": 6,
            "flight": "Q5400",
            "utc": "10:46:00"
          },
          {
            "day": 6,
            "flight": "Q5963",
            "utc": "13:53:00"
          },
          {
            "day": 6,
            "flight": "Q5195",
            "utc": "03:03:00"
          },
          {
            "day": 6,
            "flight": "Q5653",
            "utc": "22:58:00"
          }
        ],
        "sourceairport": "HKB",
        "stops": 0,
        "type": "route"
      }
    ]
  }
]

Appendix 1 - JOIN Types

Join Left-Hand Side (lhs) Right-Hand Side (rhs) Syntax Example

ANSI

Any field or expr that produces a value that will be matched on the right-hand side.

Anything that can have a proper index on the join expression.

lhs-expr

JOIN rhs-keyspace

ON any join condition

SELECT *

FROM `travel-sample` r

JOIN `travel-sample` a

ON r.airlineid = META(a).id

Lookup

Must produce a Document Key for the right-hand side.

Must have a Document Key.

lhs-expr

JOIN rhs-keyspace

ON KEYS

lhs-expr.foreign_key

SELECT *

FROM `travel-sample` r

JOIN `travel-sample` a

ON KEYS r.airlineid

Index

Must produce a key for the right-hand side’s index.

Must have a proper index on the field or expr that maps to the Document Key of the left-hand side.

lhs-keyspace

JOIN rhs-keyspace

ON KEY rhs-kspace.idx_key

FOR lhs-keyspace

SELECT

FROM `travel-sample` a

JOIN `travel-sample` r

ON KEY r.airlineid

*FOR a

Appendix 2 - NEST Types

NEST Left-Hand Side (lhs) Right-Hand Side (rhs) Syntax Example

ANSI

Any field or expr that produces a value that will be matched on the right-hand side.

Anything that can have a proper index on the join expression.

lhs-expr

NEST rhs-keyspace

ON any nest condition

SELECT *

FROM `travel-sample` r

NEST `travel-sample` a

ON r.airlineid = META(a).id

Lookup

Must produce a Document Key for the right-hand side.

Must have a Document Key.

lhs-expr

NEST rhs-keyspace

ON KEYS

lhs-expr.foreign_key

SELECT *

FROM `travel-sample` r

NEST `travel-sample` a

ON KEYS r.airlineid

WHERE r.type="route"

LIMIT 4;

Index

Must produce a key for the right-hand side index.

Must have a proper index on the field or expr that maps to the Document Key of the left-hand side.

lhs-keyspace

NEST rhs-keyspace

ON KEY rhs-kspace.idx_key

FOR lhs-keyspace

SELECT *

FROM `travel-sample` a

NEST `travel-sample` r

ON KEY r.airlineid

FOR a

WHERE a.type="airline"

LIMIT 4;