UNNEST clause

The UNNEST clause creates an input object by flattening an array in the parent document.

Purpose

The UNNEST clause is used within the FROM 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

unnest-clause ::= from-term [ unnest-type ] ( UNNEST | FLATTEN ) expr [ [ AS ] alias ]
unnest 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 UNNEST clause.

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

Unnest Type

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

This clause represents the type of unnest.

INNER

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

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.

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

Unnest Path

expr

The path to the nested array.

The path expression in each UNNEST clause must reference some preceding path.

AS Alias

Assigns another name to the right-hand side of the unnest. For details, see AS Clause.

Assigning an alias to the path is optional. If you assign an alias to the path, the AS keyword may be omitted.

Examples

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"
      }
    ]
  }
]
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;
Results
[
  "Kayli Cronin",
  "Shanelle Streich",
  "Catharine Funk",
  "Tyson Beatty"
]