Set Functions and Operators

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.

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.

db>

select 1 in {1, 3, 5};

{true}

db>

select 'Alice' in User.name;

{true}

db>

select {1, 2} in {1, 3, 5};

{true, false}

This operator can also be used to implement set intersection:

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.

db>

select 'hello' if 2 * 2 = 4 else 'bye';

{'hello'}

if..else expressions can be chained when checking multiple conditions is necessary:

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.

# 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

detached

detached set of anytype -> set of anytype

Detaches the input set reference from the current scope.

A detached expression allows referring to some set as if it were defined in the top-level with block. detached expressions ignore all current scopes in which they are nested. This makes it possible to write queries that reference the same set reference in multiple places.

update User
filter .name = 'Dave'
set {
    friends := (select detached User filter .name = 'Alice'),
    coworkers := (select detached User filter .name = 'Bob')
};

Without detached, the occurrences of User inside the set shape would be bound to the set of users named "Dave". However, in this context we want to run an unrelated query on the “unbound” User set.

# does not work!
update User
filter .name = 'Dave'
set {
    friends := (select User filter .name = 'Alice'),
    coworkers := (select User filter .name = 'Bob')
};

Instead of explicitly detaching a set, you can create a reference to it in a with block. All declarations inside a with block are implicitly detached.

with U1 := User,
     U2 := User
update User
filter .name = 'Dave'
set {
    friends := (select U1 filter .name = 'Alice'),
    coworkers := (select U2 filter .name = 'Bob')
};

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.

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 backlinks.

Consider the following types:

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):

select User.<owner;

By default backlinks 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:

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

assert_distinct()

std::assert_distinct( s: set of anytype, named only message: optional str = <str>{} ) -> set of anytype

Check that the input set contains only unique elements, i.e a proper set.

If the input set contains duplicate elements, assert_distinct raises a ConstraintViolationError. This function is useful as a runtime distinctness assertion in queries and computed expressions that should always return proper sets, but where static multiplicity inference is not capable enough or outright impossible. An optional message named argument can be used to customize the error message.

db> 
... 
... 
... 
...

select assert_distinct(
  (select User filter .groups.name = "Administrators")
  union
  (select User filter .groups.name = "Guests")
)

{default::User {id: ...}}

db> 
... 
... 
... 
...

select assert_distinct(
  (select User filter .groups.name = "Users")
  union
  (select User filter .groups.name = "Guests")
)

ERROR: ConstraintViolationError: assert_distinct violation: expression
       returned a set with duplicate elements.

db> 
... 
... 
... 
... 
...

select assert_distinct(
  (select User filter .groups.name = "Users")
  union
  (select User filter .groups.name = "Guests"),
  message := "duplicate users!"
)

ERROR: ConstraintViolationError: duplicate users!

function

assert_single()

std::assert_single( s: set of anytype, named only message: optional str = <str>{} ) -> set of anytype

Check that the input set contains no more than one element.

If the input set contains more than one element, assert_single raises a CardinalityViolationError. This function is useful as a runtime cardinality assertion in queries and computed expressions that should always return sets with at most a single element, but where static cardinality inference is not capable enough or outright impossible. An optional message named argument can be used to customize the error message.

db>

select assert_single((select User filter .name = "Unique"))

{default::User {id: ...}}

db>

select assert_single((select User))

ERROR: CardinalityViolationError: assert_single violation: more than
       one element returned by an expression

db>

select assert_single((select User), message := "too many users!")

ERROR: CardinalityViolationError: too many users!

function

assert_exists()

std::assert_exists( s: set of anytype, named only message: optional str = <str>{} ) -> set of anytype

Check that the input set contains at least one element.

If the input set is empty, assert_exists raises a CardinalityViolationError. This function is useful as a runtime existence assertion in queries and computed expressions that should always return sets with at least a single element, but where static cardinality inference is not capable enough or outright impossible. An optional message named argument can be used to customize the error message.

db>

select assert_exists((select User filter .name = "Administrator"))

{default::User {id: ...}}

db>

select assert_exists((select User filter .name = "Nonexistent"))

ERROR: CardinalityViolationError: assert_exists violation: expression
       returned an empty set.

db> 
... 
... 
...

select assert_exists(
  (select User filter .name = "Nonexistent"),
  message := "no users!"
)

ERROR: CardinalityViolationError: no users!

function

count()

std::count(s: set of anytype) -> int64

Return the number of elements in a set.

db>

select count({2, 3, 5});

{3}

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).

db>

select sum({2, 3, 5});

{10}

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.

db>

select all(<bool>{});

{true}

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.

db>

select any(<bool>{});

{false}

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.

db>

select enumerate({2, 3, 5});

{(1, 3), (0, 2), (2, 5)}

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.

db>

select min({-1, 100});

{-1}

function

max()

std::max(values: set of anytype) -> optional anytype

Return the greatest value of the input set.

db>

select max({-1, 100});

{100}