Ranges

Available since: v2.0.0

A range is composed of .. and possible delimiters to set the maximum and minimum possible values. The default syntax includes the lower limit and excludes the upper limit. A = can be used to make the upper limit inclusive, and > can be used to make the lower limit exclusive.

-- From 0 up to 9
0..10;
-- From 0 up to 10
0..=10;
-- From 1 to 9
0>..10;
-- From 1 to 10
0>..=10;

A range becomes open ended if a delimiter is not specified.

-- Anything from 0 and up
0..;
-- Anything from 1 and up
0>..;
-- Anything up to 99
..100;
-- Anything up to 100
..=100;
-- An infinite range
..;

A range can be constructed from any type of value. This is most useful when comparing one value to another.

-- All true
'g' IN 'a'..'z';
d"2024-01-01" IN d"2020-01-01"..=d"2025-01-01";
['London', d"2022-02-02", 5.7] IN ['London', d"2020-01-01"]..=['London', d"2024-12-31"];

-- All false
"ㅋㅋㅋ" IN "a".."z";
d"2028-01-01" IN d"2020-01-01"..=d"2025-01-01";
['Philadelphia', d"2022-02-02", 5.7] IN ['London', d"2020-01-01"]..=['London', d"2024-12-31"];

Ranges in FOR loops

Ranges of integers have the added convenience of being able to be used in a FOR loop.

FOR $year IN 0..=2024 {
    CREATE historical_events SET
        for_year = $year,
        events = "To be added";
}

Ranges in WHERE clauses

A range can be used in a WHERE clause in place of operators like < and >. This is especially useful when checking for a number that must be within a certain range. Using a range carries two main benefits. One is that it produces shorter code that is easier to read and maintain.

SELECT * FROM person WHERE age >= 18 AND age <= 65;
SELECT * FROM person WHERE age IN 18..=65;

Another benefit is performance. The following code should show a modest but measurable improvement in performance between the first and second SELECT statement, as only one condition needs to be checked instead of two.

DELETE person;
CREATE |person:20000| SET age = (rand::float() * 120).round() RETURN NONE;

-- Assign output to a parameter so the SELECT output is not displayed
LET $_ = SELECT * FROM person WHERE age > 18 AND age < 65;
LET $_ = SELECT * FROM person WHERE age in 18..=65;

Casting and functional usage

A range can be cast into an array.

<array> 1..3;

Output
[
	1,
	2
]

This opens up a range of functional programming patterns that are made possible by SurrealDB’s array functions, many of which can use anonymous functions (closures) to perform an operation on each item in the array.

-- Construct an array
(<array> 1..=100)
-- Turn it into an array that increments by 10
    .map(|$v| $v * 10)
-- Turn each number into a object with original and square root value
    .map(|$v| { original: $v, square_root: math::sqrt($v) })
-- Keep only those with square roots in between 11 and 12
    .filter(|$obj| $obj.square_root IN 11..12);

Output
[
	{
		original: 130,
		square_root: 11.40175425099138f
	},
	{
		original: 140,
		square_root: 11.832159566199232f
	}
]

Ranges in mock syntax for CREATE statements

Available since: v3.0.0-alpha.11

CREATE statements have always been able to work on more than one record by enclosing either a single number or a range-like operator between two || bars.

Before 3.0.0
-- Create 10 person records with random IDs
CREATE |person:10|;
-- Create `person` records from person:1 to person:10
CREATE |person:1..10|;

Originally an internal syntax for mock testing, this syntax become known to the user community and is now commonly used. However, the original syntax differed from true ranges in always being inclusive, in that 1..10 was treated as “from 1 up to and including 10”. A change has since been made to have the mock syntax take a true range with a syntax equivalent to that demonstrated in this page.

Since 3.0.0
-- All of these create ten records from person:1 to person:10
CREATE |person:1..=10|;
CREATE |person:1..11|;
CREATE |person:0>..11|;
CREATE |person:0>..=10|;