SELECT–retrieve or compute a set of values.
[ WITH with-item [, ...] ]
SELECT expr
[ FILTER filter-expr ]
[ ORDER BY order-expr [direction] [THEN ...] ]
[ OFFSET offset-expr ]
[ LIMIT limit-expr ] ;The optional FILTER clause, where filter-expr
is any expression that has a result of type bool.
The condition is evaluated for every element in the set produced by
the SELECT clause. The result of the evaluation of the
FILTER clause is a set of boolean values. If at least one value
in this set is true, the input element is included, otherwise
it is eliminated from the output.
The optional ORDER BY clause has this general form:
ORDER BY
order-expr [ ASC | DESC ] [ EMPTY { FIRST | LAST } ]
[ THEN ... ]The ORDER BY clause produces a result set sorted according
to the specified expression or expressions, which are evaluated
for every element of the input set.
If two elements are equal according to the leftmost expression, they are compared according to the next expression and so on. If two elements are equal according to all expressions, the resulting order is undefined.
Each expression can be an arbitrary expression that results in a value of an orderable type. Primitive types are orderable, object types are not. Additionally, the result of each expression must be an empty set or a singleton. Using an expression that may produce more elements is a compile-time error.
An optional ASC or DESC keyword can be added after any
expression. If not specified ASC is assumed by default.
If EMPTY LAST is specified, then input values that produce
an empty set when evaluating an expression are sorted after
all other values; if EMPTY FIRST is specified, then they
are sorted before all other values. If neither is specified,
EMPTY FIRST is assumed when ASC is specified or implied,
and EMPTY LAST when DESC is specified.
The optional OFFSET clause, where
offset-expr
is a singleton expression of an integer type.
This expression is evaluated once and its result is used
to skip the first element-count elements of the input set
while producing the output. If element-count evaluates to
an empty set, it is equivalent to OFFSET 0, which is equivalent
to omitting the OFFSET clause. If element-count evaluates
to a value that is larger then the cardinality of the input set,
an empty set is produced as the result.
The optional LIMIT clause, where limit-expr
is a singleton expression of an integer
type. This expression is evaluated once and its result is used
to include only the first element-count elements of the input set
while producing the output. If element-count evaluates to
an empty set, it is equivalent to specifying no LIMIT clause.
SELECT retrieves or computes a set of values. The data
flow of a SELECT block can be conceptualized like this:
WITH MODULE example
select clause
SELECT
expr compute a set of things
optional clause
FILTER
expr filter the computed set
optional clause
ORDER BY
expr define ordering of the filtered set
optional clause
OFFSET
expr slice the filtered/ordered set
optional clause
LIMIT
expr slice the filtered/ordered setPlease note that the ORDER BY clause defines ordering that can
only be relied upon if the resulting set is not used in any other
operation. SELECT, OFFSET and LIMIT clauses are the only
exception to that rule as they preserve the inherent ordering of the
underlying set.
The first clause is SELECT. It indicates that FILTER, ORDER
BY, OFFSET, or LIMIT clauses may follow an expression, i.e.
it makes an expression into a SELECT statement. Without any of the
optional clauses a (SELECT Expr) is completely equivalent to
Expr for any expression Expr.
Consider an example using the FILTER optional clause:
WITH MODULE example
SELECT User {
name,
owned := (SELECT
User.<owner[IS Issue] {
number,
body
}
)
}
FILTER User.name LIKE 'Alice%';The above example retrieves a single user with a specific name. The
fact that there is only one such user is a detail that can be well-
known and important to the creator of the database, but otherwise non-
obvious. However, forcing the cardinality to be at most 1 by using the
LIMIT clause ensures that a set with a single object or
{} is returned. This way any further code that relies on the
result of this query can safely assume there’s only one result
available.
WITH MODULE example
SELECT User {
name,
owned := (SELECT
User.<owner[IS Issue] {
number,
body
}
)
}
FILTER User.name LIKE 'Alice%'
LIMIT 1;Next example makes use of ORDER BY and LIMIT clauses:
WITH MODULE example
SELECT Issue {
number,
body,
due_date
}
FILTER
EXISTS Issue.due_date
AND
Issue.status.name = 'Open'
ORDER BY
Issue.due_date
LIMIT 3;The above query retrieves the top 3 open Issues with the closest due date.
The FILTER clause cannot affect anything aggregate-like in the
preceding SELECT clause. This is due to how FILTER clause
works. It can be conceptualized as a function like filter($input,
SET OF $cond), where the $input represents the value of the
preceding clause, while the $cond represents the filtering
condition expression. Consider the following:
WITH MODULE example
SELECT count(User)
FILTER User.name LIKE 'Alice%';The above can be conceptualized as:
WITH MODULE example
SELECT _filter(
count(User),
User.name LIKE 'Alice%'
);In this form it is more apparent that User is a SET OF
argument (of count()), while User.name LIKE 'Alice%' is
also a SET OF argument (of filter). So the symbol User in
these two expressions exists in 2 parallel scopes. Contrast it with:
# This will actually only count users whose name starts with
# 'Alice'.
WITH MODULE example
SELECT count(
(SELECT User
FILTER User.name LIKE 'Alice%')
);
# which can be represented as:
WITH MODULE example
SELECT count(
_filter(User,
User.name LIKE 'Alice%')
);