WITH clause
- reference
Use WITH
to create a common table expression.
The common table expression may be temporary result set that can be used as a data source for the query, or an expression for later use within a query.
Purpose
Common table expressions or CTEs can be used to simplify complex queries. They can also be particularly useful when a value needs to be used several times in a query.
The WITH clause has comparable functionality to the LET clause. The major difference between the WITH clause and the LET clause is that the WITH clause can come before the SELECT clause, enabling an earlier definition of expressions; whereas the LET clause must come after the FROM clause.
The WITH clause is evaluated once per query block, and LET is evaluated for every object produced by the FROM or JOIN clause.
You can chain WITH clauses. A CTE that you create in one WITH clause may be referenced in a later WITH clause.
Prerequisites
The WITH clause can only be used preceding a SELECT statement, and in order for you to select data from a document or keyspace, you must have the query_select
privilege on the document or keyspace.
For more details about user roles, see
Authorization.
Syntax
with-clause ::= 'WITH' alias 'AS' '(' ( select | expr ) ')'
( ',' alias 'AS' '(' ( select | expr ) ')' )*
Arguments
- alias
-
[Required] String or expression that represents the name of the variable.
- select
-
SELECT statement that returns a temporary result set assigned to
alias
. - expression
-
String or expression that represents a value assigned to
alias
.
Examples
Find the average number of public likes for each record. Then find all hotels with a greater than average number of public likes.
WITH avgLikeCount AS (
SELECT VALUE AVG(DISTINCT ARRAY_COUNT(cte.public_likes))
FROM `travel-sample`.inventory.hotel AS cte
)
SELECT hotel.name, ARRAY_COUNT(hotel.public_likes) AS likeCount
FROM `travel-sample`.inventory.hotel
WHERE ARRAY_COUNT(hotel.public_likes) > avgLikeCount[0]
LIMIT 5;
[
{
"likeCount": 8,
"name": "Medway Youth Hostel"
},
{
"likeCount": 7,
"name": "Le Clos Fleuri"
},
{
"likeCount": 9,
"name": "Windy Harbour Farm Hotel"
},
{
"likeCount": 5,
"name": "Avondale Guest House"
},
{
"likeCount": 8,
"name": "The Bulls Head"
}
]
Create a recordset of hotel names and their Cleanliness ratings. Then use this recordset to find the names all hotels whose average Cleanliness rating is greater than 4.5.
WITH hotels AS (
SELECT name, reviews[*].ratings[*].Cleanliness
FROM `travel-sample`.inventory.hotel
)
SELECT hotels.name
FROM hotels
WHERE ARRAY_AVG(hotels.Cleanliness) > 4.5
LIMIT 5;
[
{
"name": "The George Hotel"
},
{
"name": "Windy Harbour Farm Hotel"
},
{
"name": "Avondale Guest House"
},
{
"name": "The Bulls Head"
},
{
"name": "Hill House Holiday Cottage"
}
]