A newer version of this documentation is available.

View Latest

NEST Clause

      +
      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

      FROM Term

      The FROM term is a keyspace reference or expression which defines the input object(s) for the query, representing the left-hand side of the NEST clause.

      The FROM term is recursive, which enables you to chain the NEST clause with any of the terms which are permitted in the FROM clause, including other NEST clauses. 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.

      A summary of the different types of FROM term is given in the following table.

      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;

      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 ansi-nest-rhs ansi-nest-predicate
      ansi-nest-type? 'NEST' ansi-nest-rhs ansi-nest-predicate

      Nest Type

      ansi-nest-type ::= INNER | ( LEFT [ OUTER ] )
      ansi nest type

      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 as LEFT 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

      ansi-nest-rhs ::= keyspace-ref [ [ AS ] alias ]
      keyspace-ref ( 'AS'? 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
      ansi nest predicate
      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 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.

      Examples

      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

      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? 'NEST' lookup-nest-rhs lookup-nest-predicate

      Nest Type

      lookup-nest-type ::= INNER | ( LEFT [ OUTER ] )
      lookup nest type

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

      This clause is optional. If omitted, the default is INNER.

      Nest Right-Hand Side

      lookup-nest-rhs ::= keyspace-ref [ [ AS ] alias ]
      keyspace-ref ( 'AS'? 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.

      Nest Predicate

      lookup-nest-predicate ::= ON KEYS expr
      lookup nest predicate

      The ON KEYS expression produces a document key or array of document keys for the right-hand side of the lookup nest.

      expr

      [Required] String or expression representing the primary keys of the documents for the right-hand side keyspace.

      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

      Examples

      Example 2. 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

      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 2 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? 'NEST' index-nest-rhs index-nest-predicate

      Nest Type

      index-nest-type ::= INNER | ( LEFT [ OUTER ] )
      index nest type

      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 as LEFT 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 ( 'AS'? 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
      index nest predicate
      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 for alias.

      alias

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

      Examples

      Example 3. Use INDEX nest to flip the direction of Example 2 above

      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 idx_inest 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"
            }
          ]
        }
      ]

      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 left-hand side).

      • 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 (left-hand side).

      Appendix: Summary of NEST Types

      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

      SELECT *
      FROM `travel-sample` r
      NEST `travel-sample` a
      ON r.airlineid = META(a).id

      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

      SELECT *
      FROM `travel-sample` r
      NEST `travel-sample` a
      ON KEYS r.airlineid
      WHERE r.type="route"
      LIMIT 4;

      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

      SELECT *
      FROM `travel-sample` a
      NEST `travel-sample` r
      ON KEY` `r.airlineid
      FOR a
      WHERE a.type="airline"
      LIMIT 4;