Using the SET clause
The SET clause is used to define a variable that can be reused and
modified later in the query.
The SET clause should be structured as
follows:
SET $variable_name = expressionOnce a variable has been defined, you can use the same syntax to change its value.
The variable must start with the $ character and cannot be a hierarchical identifier, for example, $item.index is not a valid variable. The variable must be unique throughout the query. The expression used to specify the value of the variable can be a simple expression, a conditional expression, or an array.
The SET clause must be used after a FROM,
UNNEST, JOIN, GROUP BY, or
UNION ALL clause, or in a query block introduced by a
SELECT clause. If the query block contains a WHERE or
HAVING clause, the SET should be placed before it. For
example:
FROM customer
SET $address = concatWith(" ", address.street, address.city)
WHERE hasValue(rating)
SELECT {
name,
rating,
address = $address,
SET $level = if (rating > 650) "Premium" else "Standard",
level = $level
}Information noteNote: If a SET clause is used in a SELECT clause:
- The variable is only available in that block and cannot be used elsewhere in the query.
- Aggregation functions are not supported.