Set functions¶

Set functions are used to construct and modify data from set-based data structures (collections that do not contain duplicate elements).

set_append()¶

Appends a new element to an existing set.

Null behavior: general

Function signature¶

set_append(target_set, value) -> SET

target_set: The target set to which the new element is to be appended.
value: The value to be appended. Supports any predefined data type or constructed data type other than records and paths.

Notes¶

If the specified value already exists in the set, the function returns the original set without modification (sets do not allow duplicate elements).
The data type of the value to be appended must be the same as the data type of the existing elements in the set.
You can append an element to an empty set (SET{}) to create a new set containing a single element.
The value can be null.

Examples¶

Append a new element¶

RETURN set_append(SET{1, 2, 3}, 4)

Return:

+-----------------------------+
| set_append(SET{1, 2, 3}, 4) |
+-----------------------------+
| {1,2,3,4}                   |
+-----------------------------+

Append an existing element¶

RETURN set_append(SET{null}, null)

Return:

+------------------------------+
| set_append(SET{null}, null)  |
+------------------------------+
| {null}                       |
+------------------------------+

Append a null value¶

RETURN set_append(SET{1}, null)

Return:

+--------------------------+
| set_append(SET{1}, null) |
+--------------------------+
| {1,null}                 |
+--------------------------+

Append to an empty set¶

RETURN set_append(SET{}, null)

Return:

+--------------------------+
| set_append(SET{}, null)  |
+--------------------------+
| {null}                   |
+--------------------------+

set_union()¶

Returns the union of two sets.

Null behavior: general

Function signature¶

set_union(set1, set2) -> SET

set1: The first set.
set2: The second set.

Notes¶

Returns a new set containing all unique elements from both sets.
Functionally equivalent to the || operator.
If either argument is null, the result depends on the specific implementation.

Examples¶

Union of two sets¶

RETURN set_union(SET{1, 2, 3}, SET{3, 4, 5})

Return:

+---------------------------------------+
| set_union(SET{1, 2, 3}, SET{3, 4, 5}) |
+---------------------------------------+
| {5,4,3,2,1}                           |
+---------------------------------------+

Union with an empty set¶

RETURN set_union(SET{}, SET{1, 2})

Return:

+-----------------------------+
| set_union(SET{}, SET{1, 2}) |
+-----------------------------+
| {2,1}                       |
+-----------------------------+

Union of sets containing null¶

RETURN set_union(SET{null, null}, SET{null})

Return:

+-----------------------------------------+
| set_union(SET{null, null}, SET{null})   |
+-----------------------------------------+
| {null}                                  |
+-----------------------------------------+

set_intersect()¶

Returns the intersection of two sets.

Null behavior: general

Function signature¶

set_intersect(set1, set2) -> SET

set1: The first set.
set2: The second set.

Notes¶

Returns a new set containing elements that exist in both sets.

Examples¶

Intersection of two sets¶

RETURN set_intersect(SET{1, 2, 3}, SET{2, 3, 4})

Return:

+-------------------------------------------+
| set_intersect(SET{1, 2, 3}, SET{2, 3, 4}) |
+-------------------------------------------+
| {3,2}                                     |
+-------------------------------------------+

No common elements¶

RETURN set_intersect(SET{1, 2, 3}, SET{4, 5, 6})

Return:

+-------------------------------------------+
| set_intersect(SET{1, 2, 3}, SET{4, 5, 6}) |
+-------------------------------------------+
| {}                                        |
+-------------------------------------------+

Intersection with null¶

RETURN set_intersect(SET{1, 2, 3}, null)

Return:

+------------------------------------+
| set_intersect(SET{1, 2, 3}, null)  |
+------------------------------------+
| null                               |
+------------------------------------+

Intersection with null elements¶

'''
LET a = set_intersect(SET{1, 2, 3, null}, SET{1, 2, 3, 4})
RETURN size(a)
'''

Return:

+---------+
| size(a) |
+---------+
| 3       |
+---------+

set_except()¶

Returns the difference of two sets (elements in the first set that are not in the second set).

Null behavior: general

Function signature¶

set_except(set1, set2) -> SET

set1: The first set.
set2: The second set.

Notes¶

Returns a new set containing elements that exist in the first set but not in the second set.

Examples¶

Difference of two sets¶

RETURN set_except(SET{1, 2, 3}, SET{2, 3, 4})

Return:

+-----------------------------------------+
| set_except(SET{1, 2, 3}, SET{2, 3, 4})  |
+-----------------------------------------+
| {1}                                     |
+-----------------------------------------+

First set is a subset¶

RETURN set_except(SET{1, 2, 3}, SET{1, 2, 3, 4})

Return:

+-------------------------------------------+
| set_except(SET{1, 2, 3}, SET{1, 2, 3, 4}) |
+-------------------------------------------+
| {}                                        |
+-------------------------------------------+

Difference with null elements¶

RETURN set_except(SET{1, 2, 3, null}, SET{4})

Return:

+----------------------------------------+
| set_except(SET{1, 2, 3, null}, SET{4}) |
+----------------------------------------+
| {null,3,2,1}                           |
+----------------------------------------+

set_sum()¶

Returns the sum of all numeric elements in a set.

Null behavior: general

Function signature¶

set_sum(target_set) -> NUMERIC

target_set: The target set for which to calculate the sum.

Notes¶

Supports only numeric types and the NULL type.
null values are ignored during calculation.
If the set contains only null, returns 0.
If the set contains NaN, returns NaN.

Examples¶

Sum of numeric values¶

RETURN set_sum(SET{1.0M, 2M})

Return:

+------------------------+
| set_sum(SET{1.0M, 2M}) |
+------------------------+
| 3.0                    |
+------------------------+

Set containing only null¶

RETURN set_sum(SET{null})

Return:

+--------------------+
| set_sum(SET{null}) |
+--------------------+
| 0                  |
+--------------------+

Numeric set containing null¶

RETURN set_sum(SET{1, null})

Return:

+-----------------------+
| set_sum(SET{1, null}) |
+-----------------------+
| 1                     |
+-----------------------+

Set containing NaN¶

RETURN set_sum(SET{1.0M, 2M, NAN})

Return:

+-----------------------------+
| set_sum(SET{1.0M, 2M, NAN}) |
+-----------------------------+
| NaN                         |
+-----------------------------+

Set containing both NaN and null¶

RETURN set_sum(SET{1.0M, 2M, NAN, NULL})

Return:

+-----------------------------------+
| set_sum(SET{1.0M, 2M, NAN, NULL}) |
+-----------------------------------+
| NaN                               |
+-----------------------------------+

set_max()¶

Returns the maximum value in a set. For more information about the comparison between values, see Comparison predicate.

Null behavior: general

Function signature¶

set_max(target_set) -> NUMERIC | NULL

target_set: The target set from which to find the maximum value.

Notes¶

Supports only numeric types and the NULL type.
Returns null if the set contains null.

Examples¶

Get the maximum value¶

RETURN set_max(SET{1, 2, 3.0M})

Return:

+--------------------------+
| set_max(SET{1, 2, 3.0M}) |
+--------------------------+
| 3.0                      |
+--------------------------+

Set containing only null¶

RETURN set_max(SET{null})

Return:

+--------------------+
| set_max(SET{null}) |
+--------------------+
| null               |
+--------------------+

Set containing NaN¶

RETURN set_max(SET{1.0M, 2M, NAN})

Return:

+-----------------------------+
| set_max(SET{1.0M, 2M, NAN}) |
+-----------------------------+
| NaN                         |
+-----------------------------+

Set containing null and NaN¶

RETURN set_max(SET{1.0M, NULL, 2M, NAN})

Return:

+-----------------------------------+
| set_max(SET{1.0M, NULL, 2M, NAN}) |
+-----------------------------------+
| null                              |
+-----------------------------------+

Set of negative numbers¶

RETURN set_max(SET{-10.8, -10.7, -10.6})

Return:

+-----------------------------------+
| set_max(SET{-10.8, -10.7, -10.6}) |
+-----------------------------------+
| -10.6                             |
+-----------------------------------+

set_min()¶

Returns the minimum value in a set. For more information about the comparison between values, see Comparison predicate.

Null behavior: general

Function signature¶

set_min(target_set) -> NUMERIC | NULL

target_set: The target set from which to find the minimum value.

Notes¶

Supports only numeric types and the NULL type.
Returns null if the set contains null.

Examples¶

Get the minimum value¶

RETURN set_min(SET{1, 2, 3.0M})

Return:

+--------------------------+
| set_min(SET{1, 2, 3.0M}) |
+--------------------------+
| 1                        |
+--------------------------+

Set containing only null¶

RETURN set_min(SET{null})

Return:

+--------------------+
| set_min(SET{null}) |
+--------------------+
| null               |
+--------------------+

Set containing NaN¶

RETURN set_min(SET{1.0M, 2M, NAN})

Return:

+-----------------------------+
| set_min(SET{1.0M, 2M, NAN}) |
+-----------------------------+
| 1.0                         |
+-----------------------------+

Set containing null and NaN¶

RETURN set_min(SET{1.0M, NULL, 2M, NAN})

Return:

+-----------------------------------+
| set_min(SET{1.0M, NULL, 2M, NAN}) |
+-----------------------------------+
| null                              |
+-----------------------------------+

Set of negative numbers¶

RETURN set_min(SET{-10.8, -10.7, -10.6})

Return:

+-----------------------------------+
| set_min(SET{-10.8, -10.7, -10.6}) |
+-----------------------------------+
| -10.8                             |
+-----------------------------------+

set_filter()¶

Returns a new set containing only the elements that satisfy the specified conditions.

Null behavior: general

Function signature¶

set_filter(target_set, predicate) -> SET

target_set: The target set to filter.
predicate: A lambda expression x -> boolean_expression that defines the filter condition. x represents an element in the set.

Notes¶

If the input set is null, the function returns null.
If the input set is empty, the function returns an empty set.
The predicate is applied to each element, and only elements where the predicate evaluates to true are included in the result.

Examples¶

Filter out null elements¶

RETURN set_filter(SET{1, 2, 3, null}, x -> x IS NOT NULL)

Return:

+----------------------------------------------------+
| set_filter(SET{1, 2, 3, null}, x -> x IS NOT NULL) |
+----------------------------------------------------+
| {1,2,3}                                            |
+----------------------------------------------------+

Filter by numeric condition¶

RETURN set_filter(SET{1, 2, 3, 4}, x -> x >= 2 AND x <> 3)

Return:

+-----------------------------------------------------+
| set_filter(SET{1, 2, 3, 4}, x -> x >= 2 AND x <> 3) |
+-----------------------------------------------------+
| {2,4}                                               |
+-----------------------------------------------------+

Filter with predicate always true¶

RETURN set_filter(SET{1, 2}, x -> true)

Return:

+----------------------------------+
| set_filter(SET{1, 2}, x -> true) |
+----------------------------------+
| {1,2}                            |
+----------------------------------+

Filter by string¶

RETURN set_filter(SET{"yes", "no", "yes"}, x -> x = "yes")

Return:

+-----------------------------------------------------+
| set_filter(SET{"yes", "no", "yes"}, x -> x = "yes") |
+-----------------------------------------------------+
| {yes}                                               |
+-----------------------------------------------------+

Filter with captured variable¶

'''
FOR i IN [0, 5, 2]
RETURN set_filter(SET{1, 2, 3}, x -> x > i)
'''

Return:

+--------------------------------------+
| set_filter(SET{1, 2, 3}, x -> x > i) |
+--------------------------------------+
| {3,2,1}                              |
| {}                                   |
| {3}                                  |
+--------------------------------------+

Filter with LET variable¶

'''
LET threshold = 2
FOR s IN [SET{1, 2}, SET{1, 2, 3}]
RETURN set_filter(s, x -> x >= threshold)
'''

Return:

+------------------------------------+
| set_filter(s, x -> x >= threshold) |
+------------------------------------+
| {2}                                |
| {3,2}                              |
+------------------------------------+

Filter with null and empty set inputs¶

'''
FOR s IN [SET{-1}, NULL, SET{}, SET{2}]
RETURN set_filter(s, x -> x > 0)
'''

Return:

+---------------------------+
| set_filter(s, x -> x > 0) |
+---------------------------+
| {}                        |
| null                      |
| {}                        |
| {2}                       |
+---------------------------+

Filter null input¶

RETURN set_filter(NULL, x -> x > 1)

Return:

+------------------------------+
| set_filter(NULL, x -> x > 1) |
+------------------------------+
| null                         |
+------------------------------+

set_remove()¶

Removes the specified element from a set.

Null behavior: general

Function signature¶

set_remove(target_set, value) -> SET

target_set: The target set from which to remove the element.
value: The value to be removed. Supports any predefined data type or constructed data type other than records and paths.

Notes¶

If the specified value does not exist in the set, the function returns the original set without modification.
The data type of the value must be the same as the data type of the existing elements in the set.

Examples¶

Remove an existing element¶

RETURN set_remove(SET{1, 2, 3}, 1)

Return:

+-----------------------------+
| set_remove(SET{1, 2, 3}, 1) |
+-----------------------------+
| {3,2}                       |
+-----------------------------+

Remove a non-existent element¶

RETURN set_remove(SET{null}, 1)

Return:

+--------------------------+
| set_remove(SET{null}, 1) |
+--------------------------+
| {null}                   |
+--------------------------+

Remove from an empty set¶

RETURN set_remove(SET{}, 1)

Return:

+----------------------+
| set_remove(SET{}, 1) |
+----------------------+
| {}                   |
+----------------------+

remove_nulls()¶

Removes all null values from a set.

Null behavior: general

Function signature¶

remove_nulls(target_set) -> SET

target_set: The target set from which to remove null values.

Notes¶

Removes all elements with a value of null from the set.
If the set contains only null, returns an empty set.

Examples¶

Remove null values¶

RETURN remove_nulls(SET{1, 2, null})

Return:

+-------------------------------+
| remove_nulls(SET{1, 2, null}) |
+-------------------------------+
| {2,1}                         |
+-------------------------------+

Set containing only null¶

RETURN remove_nulls(SET{null})

Return:

+-------------------------+
| remove_nulls(SET{null}) |
+-------------------------+
| {}                      |
+-------------------------+