Operators

A variety of operators in SurrealQL allow for complex manipulation of data, and advanced logic.

&& or AND

Checks whether both of two values are truthy.

SELECT * FROM 10 AND 20 AND 30;

30

|| or OR

Checks whether either of two values are truthy.

SELECT * FROM 0 OR false OR 10;

10

!

Reverses the truthiness of a value.

SELECT * FROM !(TRUE OR FALSE)

false

SELECT * FROM !"Has a value";

false

!!

Determines the truthiness of a value (simply an application of the ! operator twice).

SELECT * FROM !!"Has a value";

true

??

Check whether either of two values are truthy and not NULL.

SELECT * FROM NULL ?? 0 ?? false ?? 10;

0

?:

Check whether either of two values are truthy.

SELECT * FROM NULL ?: 0 ?: false ?: 10;

10

= or IS

Check whether two values are equal.

SELECT * FROM true = "true";

false

SELECT * FROM 10 = "10";

false

SELECT * FROM 10 = 10.00;

true

SELECT * FROM 10 = "10.3";

false

SELECT * FROM [1, 2, 3] = [1, 2, 3];

true

SELECT * FROM [1, 2, 3] = [1, 2, 3, 4];

false

SELECT * FROM { this: "object" } = { this: "object" };

true

SELECT * FROM { this: "object" } = { another: "object" };

false

!= or IS NOT

Check whether two values are equal.

SELECT * FROM 10 != "15";

true

SELECT * FROM 10 != "test";

true

SELECT * FROM [1, 2, 3] != [3, 4, 5];

true

==

Check whether two values are exact. This operator also checks that each value has the same type.

SELECT * FROM 10 == 10;

true

SELECT * FROM 10 == "10";

false

SELECT * FROM true == "true";

false

?=

Check whether any value in an array equals another value.

SELECT * FROM [10, 15, 20] ?= 10;

true

*=

Check whether all values in an array equals another value.

SELECT * FROM [10, 10, 10] *= 10;

true

~ ?~ !~ *~

These operators used to compare two values for equality using fuzzy matching. They have been removed since 3.0 to avoid implicitly preferring one algorithm over another, as the type of fuzzy matching to use will depend on each individual case. Please use the string::similarity::* functions instead:

let $threshold = 10;
string::similarity::smithwaterman("test text","Test") > $treshold;

true

<

Check whether a value is less than another value.

SELECT * FROM 10 < 15;

true

<=

Check whether a value is less than or equal to another value.

SELECT * FROM 10 <= 15;

true

>

Check whether a value is less than another value.

SELECT * FROM 15 > 10;

true

>=

Check whether a value is less than or equal to another value.

SELECT * FROM 15 >= 10;

true

+

Add two values together.

SELECT * FROM 10 + 10;

20

SELECT * FROM "test" + " " + "this";

"test this"

SELECT * FROM 13h + 30m;

"13h30m"

-

Subtracts a value from another value.

SELECT * FROM 20 - 10;

10

SELECT * FROM 2m - 1m;

"1m"

* or ×

Multiplies a value by another value.

SELECT * FROM 20 * 2;

40

/ or ÷

Divides a value with another value.

SELECT * FROM 20 / 2;

10

**

Raises a base value by another value.

SELECT * FROM 20 ** 3;

8000

CONTAINS or ∋

Check whether a value contains another value.

SELECT * FROM [10, 20, 30] CONTAINS 10;

true

SELECT * FROM "this is some text" CONTAINS "text";

true

SELECT * FROM {
	type: "Polygon",
	coordinates: [[
		[-0.38314819, 51.37692386], [0.1785278, 51.37692386],
		[0.1785278, 51.61460570], [-0.38314819, 51.61460570],
		[-0.38314819, 51.37692386]
	]]
} CONTAINS (-0.118092, 51.509865);

true

CONTAINSNOT or ∌

Check whether a value does not contain another value.

SELECT * FROM [10, 20, 30] CONTAINSNOT 15;

true

SELECT * FROM "this is some text" CONTAINSNOT "other";

true

SELECT * FROM {
	type: "Polygon",
	coordinates: [[
		[-0.38314819, 51.37692386], [0.1785278, 51.37692386],
		[0.1785278, 51.61460570], [-0.38314819, 51.61460570],
		[-0.38314819, 51.37692386]
	]]
} CONTAINSNOT (-0.518092, 53.509865);

true

CONTAINSALL or ⊇

Check whether a value contains all of multiple values.

SELECT * FROM [10, 20, 30] CONTAINSALL [10, 20, 10];

true

CONTAINSANY or ⊃

Check whether a value contains any of multiple values.

SELECT * FROM [10, 20, 30] CONTAINSANY [10, 15, 25];

true

INSIDE or ∈ or IN

Check whether a value is contained within another value.

SELECT * FROM 10 INSIDE [10, 20, 30];

true

SELECT * FROM "text" INSIDE "this is some text";

true

SELECT * FROM (-0.118092, 51.509865) INSIDE {
	type: "Polygon",
	coordinates: [[
		[-0.38314819, 51.37692386], [0.1785278, 51.37692386],
		[0.1785278, 51.61460570], [-0.38314819, 51.61460570],
		[-0.38314819, 51.37692386]
	]]
};

true

Available since: v2.1.0

This operator can also be used to check for the existence of a key inside an object. To do so, precede IN with the field name as a string.

"name" IN {
    name: "Riga",
    country: "Latvia"
};

-- true

IN can also be used with a record ID as long as the ID is expanded to include the fields. Both of the following queries will return true.

CREATE city:riga SET name = "Riga", country = "Latvia", population = 605273;

"name" IN city:riga.*;
"name" IN city:riga.{ name, country };

NOTINSIDE or ∉ or NOT IN

Check whether a value is not contained within another value.

SELECT * FROM 15 NOTINSIDE [10, 20, 30];

true

SELECT * FROM "other" NOTINSIDE "this is some text";

true

SELECT * FROM (-0.518092, 53.509865) NOTINSIDE {
	type: "Polygon",
	coordinates: [[
		[-0.38314819, 51.37692386], [0.1785278, 51.37692386],
		[0.1785278, 51.61460570], [-0.38314819, 51.61460570],
		[-0.38314819, 51.37692386]
	]]
};

true

ALLINSIDE or ⊆

Check whether all of multiple values are contained within another value.

SELECT * FROM [10, 20, 10] ALLINSIDE [10, 20, 30];

true

ANYINSIDE or ⊂

Check whether any of multiple values are contained within another value.

SELECT * FROM [10, 15, 25] ANYINSIDE [10, 20, 30];

true

NONEINSIDE or ⊄

Check whether none of multiple values are contained within another value.

SELECT * FROM [15, 25, 35] NONEINSIDE [10, 20, 30];

true

OUTSIDE

Check whether a geometry value is outside another geometry value.

SELECT * FROM (-0.518092, 53.509865) OUTSIDE {
	type: "Polygon",
	coordinates: [[
		[-0.38314819, 51.37692386], [0.1785278, 51.37692386],
		[0.1785278, 51.61460570], [-0.38314819, 51.61460570],
		[-0.38314819, 51.37692386]
	]]
};

true

INTERSECTS

Check whether a geometry value intersects another geometry value.

SELECT * FROM {
	type: "Polygon",
	coordinates: [[
		[-0.38314819, 51.37692386], [0.1785278, 51.37692386],
		[0.1785278, 51.61460570], [-0.38314819, 51.61460570],
		[-0.38314819, 51.37692386]
	]]
} INTERSECTS {
	type: "Polygon",
	coordinates: [[
		[-0.11123657, 51.53160074], [-0.16925811, 51.51921169],
		[-0.11466979, 51.48223813], [-0.07381439, 51.51322956],
		[-0.11123657, 51.53160074]
	]]
};

true

MATCHES

Checks whether the terms are found in a full-text indexed field.

SELECT * FROM book WHERE title @@ 'rust web';


[
	{
		id: book:1,
		title: 'Rust Web Programming'
	}
]

Using the matches operator with a reference checks whether the terms are found, highlights the searched terms, and computes the full-text score.

SELECT id,
		search::highlight('<b>', '</b>', 1) AS title,
		search::score(1) AS score
FROM book
WHERE title @1@ 'rust web'
ORDER BY score DESC;

[
	{
		id: book:1,
		score: 0.9227996468544006f,
		title: '<b>Rust</b> <b>Web</b> Programming'
	}
]

Available since: v3.0.0-alpha.8

AND, OR, and numeric operators inside @@

The keywords AND and can be included inside the matches operator, while the keyword OR can also be used if an index is defined with the FULLTEXT ANALYZER clause instead of the SEARCH ANALYZER clause.

CREATE document:1 SET text = "It is rare that I find myself penning a personal note in my chronicles.";
DEFINE ANALYZER simple TOKENIZERS blank,class FILTERS lowercase;
DEFINE INDEX some_index ON document FIELDS text FULLTEXT ANALYZER simple BM25;

Manually using AND and OR outside of the matches operator has always been possible. Both of these queries will return document:1 because both words match in the first case using AND, and one matches in the second case using OR.

SELECT id FROM document WHERE text @@ 'PERSONAL' AND text @@ 'INCLUSION';
SELECT id FROM document WHERE text @@ 'PERSONAL' OR text @@ 'cat';

KNN

Available since: v1.3.0

K-Nearest Neighbors (KNN) is a fundamental algorithm used for classifying or regressing based on the closest data points in the feature space, with its performance and scalability critical in applications involving large datasets.

In practice, the efficiency and scalability of the KNN algorithm are crucial, especially when dealing with large datasets. Different implementations of KNN are tailored to optimize these aspects without compromising the accuracy of the results.

SurrealDB supports different K-Nearest Neighbors methods to perform KNN searches, each with unique requirements for syntax. Below are the details for each method, including how to format your query with examples:

Brute Force Method

Best for smaller datasets or when the highest accuracy is required.

SurrealQL Syntax
<|K,DISTANCE_METRIC|>

• K: The number of nearest neighbors to retrieve.
• DISTANCE_METRIC: The metric used to calculate distances, such as EUCLIDEAN or MANHATTAN.

CREATE pts:3 SET point = [8,9,10,11];
SELECT id FROM pts WHERE point <|2,EUCLIDEAN|> [2,3,4,5];

MTREE Index Method

Available since: v1.3.0

Ideal for larger datasets where performance is crucial, and a consistent distance metric can be predefined.

SurrealQL Syntax
<|K|>

• K: The number of nearest neighbors. The distance metric is predefined in the index, simplifying the syntax.

CREATE pts:3 SET point = [8,9,10,11];
DEFINE INDEX mt_pts ON pts FIELDS point MTREE DIMENSION 4 DIST EUCLIDEAN;
SELECT id FROM pts WHERE point <|2|> [2,3,4,5];

HNSW Method

Available since: v1.5.0

Recommended for very large datasets where speed is essential and some loss of accuracy is acceptable.

SurrealQL Syntax
<|K,EF|>

• K: The number of nearest neighbors.
• EF: The size of the dynamic candidate list during the search, affecting the search’s accuracy and speed.

CREATE pts:3 SET point = [8,9,10,11];
DEFINE INDEX mt_pts ON pts FIELDS point HNSW DIMENSION 4 DIST EUCLIDEAN EFC 150 M 12;
SELECT id FROM pts WHERE point <|10,40|> [2,3,4,5];

Types of operators, order of operations and binding power

To determine which operator is executed first, a concept called “binding power” is used. Operators with greater binding power will operate directly on their neighbours before those with lower binding power. The following is a list of all operator types from greatest to lowest binding power.

Examples of binding power

The following samples show examples of basic operations of varying binding power. The original example is followed by the same example with the parts with higher binding power in parentheses, then the final expression after the first bound portion is calculated, and finally the output.

MulDiv first, then AddSub
1 + 3 * 4;
1 + (3 * 4);
-- Final expression
1 + 12;
-- Output
13

Power first, then MulDiv
2**3 * 3;
(2**3) * 3;
-- Final expression
8*3;
-- Output
24

Unary first, then cast
<string>-4;
<string>(-4);
-- Output
"-4"

Cast first, then Power
<number>"9"**9;
(<number>"9")**9;
-- Final expression
9**9;
-- Output
387420489

AddSub first, then Relation
"c" + "at" IN "cats";
("c" + "at") IN "cats";
-- Final expression
"cat" IN "cats";
-- Output
true

And first, then Or
true AND false OR true;
(true AND false) OR true;
-- Final expression
false OR true;
-- Output
true

Unary, then Cast, then Power, then AddSub
<decimal>-4**2+4;
((<decimal>(-4))**2)+4;
-- Output
20

$param = value

If the database encounters a statement composed of a parameter name followed by = and a value, it will be considered to be part of a LET statement as opposed to the equality operator.

$name = "Trevor";
-- Binds "Trevor" to $name and returns `NONE`
-- Does not return `true`
$name = "Trevor";

To compare for equality in this case, the statement order can be reversed or the == operator can be used.

$name = "Trevor";
-- Both return `true`
"Trevor" = $name;
$name == "Trevor";