Set Aggregates and Operators

DISTINCT set

Return a set without repeating any elements.

anytype IN set

Test the membership of an element in a set.

set UNION set

Merge two sets.

EXISTS set

Test whether a set is not empty.

set IF bool ELSE set

Conditionally provide one or the other result.

OPTIONAL anytype ?? set

Coalesce.

anytype [IS type]

Filter the set based on type.

count()

Return the number of elements in a set.

array_agg()

Return an array made from all of the input set elements.

sum()

Return the sum of the set of numbers.

all()

Generalized boolean AND applied to the set of values.

any()

Generalized boolean OR applied to the set of values.

enumerate()

Return a set of tuples of the form (index, element).

min()

Return the smallest value of the input set.

max()

Return the greatest value of the input set.

math::mean()

Return the arithmetic mean of the input set.

math::stddev()

Return the sample standard deviation of the input set.

math::stddev_pop()

Return the population standard deviation of the input set.

math::var()

Return the sample variance of the input set.

math::var_pop()

Return the population variance of the input set.

operator
DISTINCT set
DISTINCT SET OF anytype -> SET OF anytype

Return a set without repeating any elements.

DISTINCT is a set operator that returns a new set where no member is equal to any other member.

Copy
db> 
SELECT DISTINCT {1, 2, 2, 3};
{1, 2, 3}
operator
anytype IN set
anytype IN SET OF anytype -> boolanytype NOT IN SET OF anytype -> bool

Test the membership of an element in a set.

Set membership operators IN and NOT IN that test for each element of A whether it is present in B.

Copy
db> 
SELECT 1 IN {1, 3, 5};
{true}
Copy
db> 
SELECT 'Alice' IN User.name;
{true}
Copy
db> 
SELECT {1, 2} IN {1, 3, 5};
{true, false}

This operator can also be used to implement set intersection:

Copy
db> 
... 
... 
... 
WITH
    A := {1, 2, 3, 4},
    B := {2, 4, 6}
SELECT A FILTER A IN B;
{2, 4}
operator
set UNION set
SET OF anytype UNION SET OF anytype -> SET OF anytype

Merge two sets.

Since EdgeDB sets are formally multisets, UNION is a multiset sum, so effectively it merges two multisets keeping all of their members.

For example, applying UNION to {1, 2, 2} and {2}, results in {1, 2, 2, 2}.

If you need a distinct union, wrap it with DISTINCT.

operator
set IF bool ELSE set
SET OF anytype IF bool ELSE SET OF anytype -> SET OF anytype

Conditionally provide one or the other result.

left_expr IF condition ELSE right_expr

If condition is true, then the value of the IF..ELSE expression is the value of left_expr; if condition is false, the result is the value of right_expr.

Copy
db> 
SELECT 'hello' IF 2 * 2 = 4 ELSE 'bye';
{'hello'}

IF..ELSE expressions can be chained when checking multiple conditions is necessary:

Copy
db> 
... 
... 
... 
... 
WITH color := 'yellow'
SELECT 'Apple' IF color = 'red' ELSE
       'Banana' IF color = 'yellow' ELSE
       'Orange' IF color = 'orange' ELSE
       'Other';
{'Banana'}
operator
OPTIONAL anytype ?? set
OPTIONAL anytype ?? SET OF anytype -> SET OF anytype

Coalesce.

Evaluate to A for non-empty A, otherwise evaluate to B.

A typical use case of the coalescing operator is to provide default values for optional properties.

Copy
# Get a set of tuples (<issue name>, <priority>)
# for all issues.
SELECT (Issue.name, Issue.priority.name ?? 'n/a');

Without the coalescing operator the above query would skip any Issue without priority.

operator
EXISTS set
EXISTS SET OF anytype -> bool

Test whether a set is not empty.

EXISTS is an aggregate operator that returns a singleton set {true} if the input set is not empty and returns {false} otherwise.

Copy
db> 
SELECT EXISTS {1, 2};
{true}
operator
anytype [IS type]
anytype [IS type] -> anytype

Filter the set based on type.

The type intersection operator removes all elements from the input set that aren’t of the specified type. Additionally, since it guarantees the type of the result set, all the links and properties associated with the specified type can now be used on the resulting expression. This is especially useful in combination with backward links.

Consider the following types:

Copy
type User {
    required property name -> str;
}

abstract type Owned {
    required link owner -> User;
}

type Issue extending Owned {
    required property title -> str;
}

type Comment extending Owned {
    required property body -> str;
}

The following expression will get all Objects owned by all users (if there are any):

Copy
SELECT User.<owner;

By default backward links don’t infer any type information beyond the fact that it’s an Object. To ensure that this path specifically reaches Issue the type intersection operator must be used:

Copy
SELECT User.<owner[IS Issue];

# With the use of type intersection it's possible to refer to
# specific property of Issue now:
SELECT User.<owner[IS Issue].title;
function
count()
std::count(s: SET OF anytype) -> int64

Return the number of elements in a set.

Copy
db> 
SELECT count({2, 3, 5});
{3}
Copy
db> 
SELECT count(User);  # number of User objects in db
{4}
function
sum()
std::sum(s: SET OF int32) -> int64std::sum(s: SET OF int64) -> int64std::sum(s: SET OF float32) -> float32std::sum(s: SET OF float64) -> float64std::sum(s: SET OF bigint) -> bigintstd::sum(s: SET OF decimal) -> decimal

Return the sum of the set of numbers.

The result type depends on the input set type. The general rule is that the type of the input set is preserved (as if a simple + was used) while trying to reduce the chance of an overflow (so all integers produce int64 sum).

Copy
db> 
SELECT sum({2, 3, 5});
{10}
Copy
db> 
SELECT sum({0.2, 0.3, 0.5});
{1.0}
function
all()
std::all(values: SET OF bool) -> bool

Generalized boolean AND applied to the set of values.

The result is true if all of the values are true or the set of values is {}. Return false otherwise.

Copy
db> 
SELECT all(<bool>{});
{true}
Copy
db> 
SELECT all({1, 2, 3, 4} < 4);
{false}
function
any()
std::any(values: SET OF bool) -> bool

Generalized boolean OR applied to the set of values.

The result is true if any of the values are true. Return false otherwise.

Copy
db> 
SELECT any(<bool>{});
{false}
Copy
db> 
SELECT any({1, 2, 3, 4} < 4);
{true}
function
enumerate()
std::enumerate(values: SET OF anytype) -> SET OF tuple<int64, anytype>

Return a set of tuples of the form (index, element).

The enumerate() function takes any set and produces a set of tuples containing the zero-based index number and the value for each element.

The ordering of the returned set is not guaranteed, however the assigned indexes are guaranteed to be in order of the original set.

Copy
db> 
SELECT enumerate({2, 3, 5});
{(1, 3), (0, 2), (2, 5)}
Copy
db> 
SELECT enumerate(User.name);
{(0, 'Alice'), (1, 'Bob'), (2, 'Dave')}
function
min()
std::min(values: SET OF anytype) -> OPTIONAL anytype

Return the smallest value of the input set.

Copy
db> 
SELECT min({-1, 100});
{-1}
function
max()
std::max(values: SET OF anytype) -> OPTIONAL anytype

Return the greatest value of the input set.

Copy
db> 
SELECT max({-1, 100});
{100}
Light
Dark
System