LET clause
Use LET
to create variables for later use within a query.
(Introduced in Couchbase Server 4.0)
Purpose
In a query expression, it is sometimes useful to store the result of a sub-expression in order to use it in subsequent clauses.
You can do this with the LET
keyword, which creates a new variable and initializes it with the result of the expression you supply.
You can use the LET
clause within an array, in a for-loop, or independently.
For example, create a variable for the minimum latitude and set it to 71 (detailed in Example 1):
SELECT airportname, geo.lat FROM `travel-sample` LET minimum_lat = 71 WHERE geo.lat > minimum_lat;
Without the LET
clause, your complex queries would need to be divided into two separate queries:
-
One query to get a particular value (or set of values), and
-
One query to use the value (or values) from the first query.
If the LET
variable is referenced in the WHERE
clause, then it is evaluated before the WHERE
clause; otherwise, it is evaluated after the WHERE
clause.
The LET
clause can’t be chained and can’t be referenced by another LET
variable.
Each LET
alias needs to be unique within its scope.
Prerequisites
The LET
clause can only be used in 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.
Arguments
- alias
-
[Required] String or expression that represents the name of the variable.
- expr
-
[Required] String or expression that represents the value assigned to its
alias
.
Examples
Example 1: Use LET to set variables to a number, an operation on a field, and a subquery.
Find all airports and cities between certain latitudes in a country with a landmark.
SELECT t1.airportname, t1.geo.lat, t1.geo.lon, t1.city, t1.type FROM `travel-sample` t1 LET min_lat = 71, max_lat = ABS(t1.geo.lon)*4+1, place = (SELECT RAW t2.country FROM `travel-sample` t2 WHERE t2.type = "landmark") WHERE t1.type = "airport" AND t1.geo.lat > min_lat AND t1.geo.lat < max_lat AND t1.country IN place;
Results:
[ { "airportname": "Wiley Post Will Rogers Mem", "city": "Barrow", "lat": 71.285446, "type": "airport" }, { "airportname": "Dillant Hopkins Airport", "city": "Keene", "lat": 72.270833, "type": "airport" } ]
Example 2: Use LET to set a variable in an array.
Find all Sunday flights (day = 0
) to the Charles De Gaulle airport (CDG
) on Air India (AI
) airlines.
In this example, the LET variable sch is not used in the WHERE clause but used only in the projection.
Therefore, the Query Planner defers the evaluation to post-predicate evaluation, so there is no overhead for documents that are not qualified by the predicates.
|
SELECT t1.airline, t1.destinationairport, sch AS schedule FROM `travel-sample` AS t1 LET sch = ARRAY v FOR v IN t1.schedule WHEN v.day = 0 END WHERE type = "route" AND t1.destinationairport = "CDG" AND t1.airline = "AI";
Results:
[ { "airline": "AI", "destinationairport": "CDG", "schedule": [ { "day": 0, "flight": "AI988", "utc": "00:24:00" }, { "day": 0, "flight": "AI972", "utc": "17:32:00" } ] } ]