UNNEST clause
- reference
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.
To return the position of the elements in an unnested array after you use UNNEST , use the UNNEST_POS function.
|
Syntax
unnest-clause ::= unnest-type? ( 'UNNEST' | 'FLATTEN' ) expr ( 'AS'? alias )?
unnest-type | |
expr | |
alias |
Left-Hand Side
The UNNEST
clause cannot be the first term within the FROM
clause; it must be preceded by another FROM term.
The term immediately preceding the UNNEST
clause represents the left-hand side of the UNNEST
clause.
You can chain the UNNEST
clause with any of the other permitted FROM terms, including another UNNEST
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 UNNEST
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 |
---|---|
|
|
|
|
|
|
|
Unnest Type
unnest-type ::= 'INNER' | ( 'LEFT' 'OUTER'? )
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
asLEFT 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.
If you want to use an ARRAY index for the UNNEST query, you can use any arbitrary alias for the right side of the UNNEST — the alias does not have to be the same as the ARRAY index variable name in order to use that index. |
Limitations
You may chain UNNEST clauses 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.
In the route
keyspace, flatten the schedule array to get details of the flights on Monday (1
).
SELECT route.sourceairport, route.destinationairport, sched.flight, sched.utc
FROM route
UNNEST schedule sched
WHERE sched.day = 1
LIMIT 3;
[
{
"destinationairport": "MRS",
"flight": "AF356",
"sourceairport": "TLV",
"utc": "12:40:00"
},
{
"destinationairport": "MRS",
"flight": "AF480",
"sourceairport": "TLV",
"utc": "08:58:00"
},
{
"destinationairport": "MRS",
"flight": "AF250",
"sourceairport": "TLV",
"utc": "12:59:00"
}
]
Another way to get similar results is by using a quantified expression to find array items that meet our criteria:
SELECT route.sourceairport, route.destinationairport,
ARRAY item FOR item IN schedule WHEN item.day = 1 END AS Monday_flights
FROM route
WHERE 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"
}
],
"destinationairport": "MRS",
"sourceairport": "TLV"
},
{
"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"
}
],
"destinationairport": "NCE",
"sourceairport": "TLV"
},
{
"Monday_flights": [
{
"day": 1,
"flight": "AF975",
"utc": "11:23:00"
},
{
"day": 1,
"flight": "AF225",
"utc": "16:05:00"
}
],
"destinationairport": "CDG",
"sourceairport": "TNR"
}
]
UNNEST
to collect items from one array to use in another queryIn 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 hotel
UNNEST reviews AS r
WHERE r.ratings.Rooms < 2
LIMIT 4;
[
"Kayli Cronin",
"Shanelle Streich",
"Catharine Funk",
"Tyson Beatty"
]