SELECT statement

The SELECT statement can be used for selecting and querying data in a database. Each SELECT statement supports selecting from multiple targets, which can include tables, records, edges, subqueries, parameters, arrays, objects, and other values.

In the Learn more section, you can find a video that explains how to use the SELECT statement to retrieve and query data from SurrealDB.

Statement syntax

SurrealQL Syntax
SELECT [ VALUE ] @fields [ AS @alias ]
	[ OMIT @fields ...]
	FROM [ ONLY ] @targets
	[ WITH [ NOINDEX | INDEX @indexes ... ]]
	[ WHERE @conditions ]
	[ SPLIT [ ON ] @field ... ]
	[ GROUP [ BY ] @fields ... ]
	[ ORDER [ BY ]
		@fields [
			RAND()
			| COLLATE
			| NUMERIC
		] [ ASC | DESC ] ...
	]
	[ LIMIT [ BY ] @limit ]
	[ START [ AT ] @start ]
	[ FETCH @fields ... ]
	[ TIMEOUT @duration ]
	[ PARALLEL ]
	[ TEMPFILES ]
	[ EXPLAIN [ FULL ]]
;

Example usage

Basic usage

By default, SurrealDB returns an array of JSON-like objects called records instead of a tabular structure of rows and columns.

CREATE person:tobie SET
	name.first = "Tobie",
	address = "1 Bagshot Row",
	email = "tobie@surrealdb.com";

-- Select all fields from a table
SELECT * FROM person;

-- Select specific fields from a table
SELECT name, address, email FROM person;

-- Select all fields from a specific record
SELECT * FROM person:tobie;

-- Select specific fields from a specific record
SELECT name, address, email FROM person:tobie;

-- Select just a single record
-- Using the ONLY keyword, just an object
-- for the record in question will be returned.
-- This, instead of an array with a single object.
SELECT * FROM ONLY person:tobie;

An alias can be used to rename fields or change the structure of an object.

SELECT * FROM person;

-- Field `address` now shows up as "string::uppercase"
-- name.first structure now flattened into a simple field
SELECT
	name.first AS user_name,
	string::uppercase(address)
FROM person;

-- "Morgan Hitchcock" added to `name` field structure,
-- `angry_address` for field name instead of automatically
-- generated "string::uppercase(address) + '!!!'"
SELECT
	name.first,
	"Morgan Hitchcock" AS name.last,
	string::uppercase(address) + "!!!" AS angry_address
FROM person;

Output
-------- Query --------

[
	{
		address: '1 Bagshot Row',
		email: 'tobie@surrealdb.com',
		id: person:tobie,
		name: {
			first: 'Tobie'
		}
	}
]

-------- Query --------

[
	{
		"string::uppercase": '1 BAGSHOT ROW',
		user_name: 'Tobie'
	}
]

-------- Query --------

[
	{
		angry_address: '1 BAGSHOT ROW!!!',
		name: {
			first: 'Tobie',
			last: 'Morgan Hitchcock'
		}
	}
]

SurrealDB can also return specific fields as an array of values instead of the default array of objects. This only works if you select a single un-nested field from a table or a record.

-- Select the values of a single field from a table
SELECT VALUE name FROM person;

-- Select the values of a single field from a specific record
SELECT VALUE name FROM person:00e1nc508h9f7v63x72O;

Advanced expressions

SELECT queries support advanced expression in the field projections.

-- Select nested objects/values
SELECT address.city FROM person;

-- Select all nested array values
-- note the .* syntax works to select everything from an array or object-like values
SELECT address.*.coordinates AS coordinates FROM person;
-- Equivalent to
SELECT address.coordinates AS coordinates FROM person;

-- Select one item from an array
SELECT address.coordinates[0] AS latitude FROM person;

-- Select unique values from an array
SELECT array::distinct(tags) FROM article;

-- Select unique values from a nested array across an entire table
SELECT array::group(tags) AS tags FROM article GROUP ALL;

-- Use mathematical calculations in a select expression
SELECT
	(( celsius * 1.8 ) + 32) AS fahrenheit
	FROM temperature;

-- Return boolean expressions with an alias
SELECT rating >= 4 as positive FROM review;

-- Select manually generated object structure
SELECT
	{ weekly: false, monthly: true } AS `marketing settings`
FROM user;

-- Select filtered nested array values
SELECT address[WHERE active = true] FROM person;

-- Select a person who has reacted to a post using a celebration
-- Path can be conceptualized as:
-- person->(reacted_to WHERE type='celebrate')->post
SELECT * FROM person WHERE ->(reacted_to WHERE type='celebrate')->post;

-- Select a remote field from connected out graph edges
SELECT ->likes->friend.name AS friends FROM person:tobie;

-- Use the result of a subquery as a returned field
SELECT *, (SELECT * FROM events WHERE type = 'activity' LIMIT 5) AS history FROM user;

-- Restructure objects in a select expression after `.` operator (since version 2.0.0)
SELECT address.{city, country} FROM person;

Using parameters

Parameters can be used like variables to store a value which can then be used in a subsequent query.

More info on the $parent parameter in the second example can be seen on the page for predefined variables.

-- Store the subquery result in a variable and query that result.
LET $history = SELECT * FROM events WHERE type = 'activity' LIMIT 5;
SELECT * from $history;

-- Use the parent instance's field in a subquery (predefined variable)
SELECT *, (SELECT * FROM events WHERE host == $parent.id) AS hosted_events FROM user;

Numeric ranges in a WHERE clause

Available since: v2.0.0

A numeric range inside a WHERE clause can improve performance if the range is able to replace multiple checks on a certain condition. 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;

A numeric range inside a WHERE also tends to produce 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;

Record ranges

SurrealDB supports the ability to query a range of records, using the record ID. The record ID ranges, retrieve records using the natural sorting order of the record IDs. These range queries can be used to query a range of records in a timeseries context. You can see more here about array-based Record IDs.

-- Select all person records with IDs between the given range
SELECT * FROM person:1..1000;
-- Select all records for a particular location, inclusive
SELECT * FROM temperature:['London', NONE]..=['London', time::now()];
-- Select all temperature records with IDs less than a maximum value
SELECT * FROM temperature:..['London', '2022-08-29T08:09:31'];
-- Select all temperature records with IDs greater than a minimum value
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..;
-- Select all temperature records with IDs between the specified range
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..['London', '2022-08-29T08:09:31'];

Using a record range is more performant than the WHERE clause, as it does not require a table scan.

-- Create 5000 `person` records
CREATE |person:1..5000| RETURN NONE;

-- Set the starting time
LET $now = time::now();
-- Put the output somewhere so it won't clutter the screen
LET $_ = SELECT * FROM person:1..5000;
-- Get the elapsed time
LET $time1 = time::now() - $now;

LET $now = time::now();
LET $_ = SELECT * FROM person WHERE id >= 1 and id <= 5000;
LET $time2 = time::now() - $now;
RETURN [$time1, $time2];

Skip certain fields using the OMIT clause

Sometimes, especially with tables containing numerous columns, it is desirable to select all columns except a few specific ones. The OMIT clause can be used in this case.

CREATE person:tobie SET
	name = 'Tobie',
	password = '123456',
	opts.security = 'secure',
	opts.enabled = true;
CREATE person:jaime SET
	name = 'Jaime',
	password = 'asdfgh',
	opts.security = 'secure',
	opts.enabled = false;

SELECT * FROM person;
-- Omit the password field and security field in the options object
SELECT * OMIT password, opts.security FROM person;

-- Using destructuring syntax (since 2.0.0)
SELECT * OMIT password, opts.{ security, enabled } FROM person;

More on using the FROM clause

The FROM clause can be used on targets beyond just a single table or record name.

-- Selects all records from both 'user' and 'admin' tables.
SELECT * FROM user, admin;

-- Selects all records from the table named in the variable '$table',
-- but only if the 'admin' field of those records is true.
-- Equivalent to 'SELECT * FROM user WHERE admin = true'.
LET $table = "user";
SELECT * FROM type::table($table) WHERE admin = true;

-- Selects a single record from:
-- * the table named in the variable '$table',
-- * and the identifier named in the variable '$id'.
-- This query is equivalent to 'SELECT * FROM user:admin'.
LET $table = "user";
LET $id = "admin";
SELECT * FROM type::thing($table, $id);

-- Selects all records for specific users 'tobie' and 'jaime',
-- as well as all records for the company 'surrealdb'.
SELECT * FROM user:tobie, user:jaime, company:surrealdb;

-- Selects records from a list of identifiers. The identifiers can be numerical,
-- string, or specific records such as 'person:lrym5gur8hzws72ux5fa'.
SELECT * FROM [3648937, "test", person:lrym5gur8hzws72ux5fa, person:4luro9170uwcv1xrfvby];

-- Selects data from an object that includes a 'person' key,
-- which is associated with a specific person record, and an 'embedded' key set to true.
SELECT * FROM { person: person:lrym5gur8hzws72ux5fa, embedded: true };

-- This command first performs a subquery, which selects all 'user' records and adds a
-- computed 'adult' field that is true if the user's 'age' is 18 or older.
-- The main query then selects all records from this subquery where 'adult' is true.
SELECT * FROM (SELECT age >= 18 AS adult FROM user) WHERE adult = true;

Filter queries using the WHERE clause

As with traditional SQL queries, a SurrealDB SELECT query supports conditional filtering using a WHERE clause. If the expression in the WHERE clause evaluates to true, then the respective record will be returned.

-- Simple conditional filtering
SELECT * FROM article WHERE published = true;

-- Conditional filtering based on graph edges
SELECT * FROM profile WHERE count(->experience->organisation) > 3;

-- Conditional filtering based on graph edge properties
SELECT * FROM person WHERE ->(reaction WHERE type='celebrate')->post;

-- Conditional filtering with boolean logic
SELECT * FROM user WHERE (admin AND active) OR owner = true;

-- Select filtered nested array values
SELECT address[WHERE active = true] FROM person;

The SPLIT clause

As SurrealDB supports arrays and nested fields within arrays, it is possible to split the result on a specific field name, returning each value in an array as a separate value, along with the record content itself. This is useful in data analysis contexts.

CREATE user SET
    name = "Name",
    emails = ["me@me.com", "longer_email@other_service.com"];

-- Split the results by each value in an array
SELECT * FROM user SPLIT emails;

Output
[
	{
		emails: 'me@me.com',
		id: user:tr5sxe8iygdco05faoh0,
		name: 'Name'
	},
	{
		emails: 'longer_email@other_service.com',
		id: user:tr5sxe8iygdco05faoh0,
		name: 'Name'
	}
]

Other examples using the SPLIT clause:

-- Split the results by each value in a nested array
SELECT * FROM country SPLIT locations.cities;

-- Filter the result of a subquery
SELECT * FROM (SELECT * FROM person SPLIT loggedin) WHERE loggedin > '2023-05-01';

The GROUP BY and GROUP ALL clause

SurrealDB supports data aggregation and grouping, with support for multiple fields, nested fields, and aggregate functions. In SurrealDB, every field which appears in the field projections of the select statement (and which is not an aggregate function), must also be present in the GROUP BY clause.

-- Group records by a single field
SELECT country FROM user GROUP BY country;

-- Group results by a nested field
SELECT settings.published FROM article GROUP BY settings.published;

-- Group results by multiple fields
SELECT gender, country, city FROM person GROUP BY gender, country, city;

-- Use an aggregate function to select unique values from a nested array across an entire table
SELECT array::group(tags) AS tags FROM article GROUP ALL;

A longer example of grouping using aggregate functions:

INSERT INTO person [
    { gender: "M", age: 20, country: "Japan" },
    { gender: "M", age: 25, country: "Japan" },
    { gender: "F", age: 23, country: "US" },
    { gender: "F", age: 30, country: "US" },
    { gender: "F", age: 25, country: "Korea" },
    { gender: "F", age: 45, country: "UK" },
];

SELECT
	count() AS total,
	math::mean(age) AS average_age,
	gender,
	country
FROM person
GROUP BY gender, country;

-- Get the total number of records in a table
SELECT count() AS number_of_records FROM person GROUP ALL;

Output
-------- Query --------

[
	{
		average_age: 25,
		country: 'Korea',
		gender: 'F',
		total: 1
	},
	{
		average_age: 45,
		country: 'UK',
		gender: 'F',
		total: 1
	},
	{
		average_age: 26,
		country: 'US',
		gender: 'F',
		total: 2
	},
	{
		average_age: 22,
		country: 'Japan',
		gender: 'M',
		total: 2
	}
]

-------- Query --------

[
	{
		number_of_records: 6
	}
]

Sort records using the ORDER BY clause

To sort records, SurrealDB allows ordering on multiple fields and nested fields. Use the ORDER BY clause to specify a comma-separated list of field names that should be used to order the resulting records. The ASC and DESC keywords can be used to specify whether results should be sorted in an ascending or descending manner. The COLLATE keyword can be used to use Unicode collation when ordering text in string values, ensuring that different cases, and different languages are sorted in a consistent manner. Finally, the NUMERIC can be used to correctly sort text which contains numeric values.

-- Order records randomly
SELECT * FROM user ORDER BY rand();

-- Order records descending by a single field
SELECT * FROM song ORDER BY rating DESC;

-- Order records by multiple fields independently
SELECT * FROM song ORDER BY artist ASC, rating DESC;

-- Order text fields with Unicode collation
SELECT * FROM article ORDER BY title COLLATE ASC;

-- Order text fields with which include numeric values
SELECT * FROM article ORDER BY title NUMERIC ASC;

The LIMIT clause

To limit the number of records returned, use the LIMIT clause.

-- Select only the top 50 records from the person table
SELECT * FROM person LIMIT 50;

When using the LIMIT clause, it is possible to paginate results by using the START clause to start from a specific record from the result set.

-- Start at record 50 and select the following 50 records
SELECT * FROM user LIMIT 50 START 50;

The LIMIT clause followed by 1 is often used along with the ONLY clause to satisfy the requirement that only up to a single record can be returned.

-- Record IDs are unique so guaranteed to be no more than 1
SELECT * FROM ONLY person:jamie;

-- Error because no guarantee that this will return a single record
SELECT * FROM ONLY person WHERE name = "Jaime";

-- Add `LIMIT 1` to ensure that only up to one record will be returned
SELECT * FROM ONLY person WHERE name = "Jaime" LIMIT 1;

Connect targets using the FETCH clause

One of the most powerful functions in SurrealDB are the record links and graph connections.

Instead of pulling data from multiple tables and merging that data together, SurrealDB allows you to traverse related records efficiently without needing to use JOINs.

To fetch and replace records with the remote record data, use the FETCH clause to specify the fields and nested fields which should be fetched in-place, and returned in the final statement response output.

-- Select all the review information
-- and the artist's email from the artist table
SELECT *, artist.email FROM review FETCH artist;

-- Select all the article information
-- only if the author's age (from the author table) is under 30.
SELECT * FROM article WHERE author.age < 30 FETCH author;

The TIMEOUT clause

When processing a large result set with many interconnected records, it is possible to use the TIMEOUT keyword to specify a timeout duration for the statement. If the statement continues beyond this duration, then the transaction will fail, and the statement will return an error.

-- Cancel this conditional filtering based on graph edge properties
-- if it's not finished within 5 seconds
SELECT * FROM person WHERE ->knows->person->(knows WHERE influencer = true) TIMEOUT 5s;

The PARALLEL clause

When processing a large result set with many interconnected records, it is possible to use the PARALLEL keyword to specify that the statement should be processed concurrently, rather than sequentially.

-- Fetch and process the person, purchased and product targets in parallel
-- Select every product that was purchased by a person that purchased a product that person tobie also purchased
SELECT ->purchased->product<-purchased<-person->purchased->product FROM person:tobie PARALLEL;

The TEMPFILES clause

Available since: v2.0.0

When processing a large result set with many records, it is possible to use the TEMPFILES clause to specify that the statement should be processed in temporary files rather than memory.

This significantly reduces memory usage in exchange for slower performance.

-- Select every person and order them by name using temporary files rather than memory.
SELECT * FROM person ORDER BY name TEMPFILES;

This requires the temporary directory to be set in the server configuration or when using the surreal start command.

The EXPLAIN clause

When EXPLAIN is used, the SELECT statement returns an explanation, essentially revealing the execution plan to provide transparency and understanding of the query performance. EXPLAIN can be followed by FULL to see the number of executed rows.

Here is the result when the field ‘email’ is not indexed. We can see that the execution plan will iterate over the whole table.

CREATE person:tobie SET
	name = "Tobie",
	address = "1 Bagshot Row",
	email = "tobie@surrealdb.com";

SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN;
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN FULL;

Output
-------- Query --------

[
	{
		detail: {
			table: 'person'
		},
		operation: 'Iterate Table'
	},
	{
		detail: {
			type: 'Memory'
		},
		operation: 'Collector'
	}
]

-------- Query --------

[
	{
		detail: {
			table: 'person'
		},
		operation: 'Iterate Table'
	},
	{
		detail: {
			type: 'Memory'
		},
		operation: 'Collector'
	},
	{
		detail: {
			count: 1
		},
		operation: 'Fetch'
	}
]

Here is the result when the ‘email’ field is indexed. We can see that the execution plan will proceed by utilizing the index.

DEFINE INDEX fast_email ON TABLE person FIELDS email;

CREATE person:tobie SET
	name = "Tobie",
	address = "1 Bagshot Row",
	email = "tobie@surrealdb.com";

SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN;
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN FULL;

Output
-------- Query --------

[
	{
		detail: {
			plan: {
				index: 'fast_email',
				operator: '=',
				value: 'tobie@surrealdb.com'
			},
			table: 'person'
		},
		operation: 'Iterate Index'
	},
	{
		detail: {
			type: 'Memory'
		},
		operation: 'Collector'
	}
]

-------- Query --------

[
	{
		detail: {
			plan: {
				index: 'fast_email',
				operator: '=',
				value: 'tobie@surrealdb.com'
			},
			table: 'person'
		},
		operation: 'Iterate Index'
	},
	{
		detail: {
			type: 'Memory'
		},
		operation: 'Collector'
	},
	{
		detail: {
			count: 1
		},
		operation: 'Fetch'
	}
]

The WITH clause

The query planner can replace the standard table iterator with one or several index iterators based on the structure and requirements of the query. However, there may be situations where manual control over these potential optimizations is desired or required.

For instance, the cardinality of an index can be high, potentially even equal to the number of records in the table. The sum of the records iterated by several indexes may end up being larger than the number of records obtained by iterating over the table. In such cases, if there are different index possibilities, the most probable optimal choice would be to use the index known with the lowest cardinality.

• WITH INDEX @indexes ... restricts the query planner to using only the specified index(es)
• WITH NOINDEX forces the query planner to use the table iterator.

-- forces the query planner to use the specified index(es):
SELECT * FROM person
WITH INDEX ft_email
WHERE
	email = 'tobie@surrealdb.com' AND
	company = 'SurrealDB';

-- forces the usage of the table iterator
SELECT name FROM person WITH NOINDEX WHERE job = 'engineer' AND gender = 'm';

The ONLY clause

If you are selecting just one single resource, it’s possible to use the ONLY clause to filter that result from an array.

SELECT * FROM ONLY person:john;

If you are selecting from a resource where it is possible that multiple resources are returned, it is required to LIMIT the result to just one. This is needed, because the query would otherwise not be deterministic.

-- Fails
SELECT * FROM ONLY table_name;
-- Succeeds
SELECT * FROM ONLY table_name LIMIT 1;

The VERSION clause

Available since: v2.0.0

When you are starting a new database with SurrealKV as the storage engine, you can specify a version for each record. This is useful for time-travel queries. You can query a specific version of a record by using the VERSION clause. The VERSION clause is always followed by a datetime and when the specified timestamp does not exist, an empty array is returned.

Note

The VERSION clause is currently in alpha and is subject to change. We do not recommend this for production.

-- Create a new record
CREATE user:john SET name = 'John' VERSION d'2024-08-19T08:00:00Z';
[[{ id: user:john, name: 'John' }]]

-- Select the record as it is now
SELECT * FROM user:john;
[[{ id: user:john, name: 'John' }]]

-- Select the record as it was at a specific point in time
SELECT * FROM user:john VERSION d'2024-08-19T08:00:00Z';
[[{ id: user:john, name: 'John' }]]

-- Select the record as it was at a specific point in time that doesn't exist
SELECT * FROM user:john VERSION d'2024-08-19T07:00:00Z';
[[]]

-- Update the record to the user john
update user:john Set hight ="55"
[[{ hight: '55', id: user:john, name: 'John' }]]

-- Confirm that the record is updated
SELECT * FROM user:john;
[[{ hight: '55', id: user:john, name: 'John' }]]

-- Select the record for the timestamp before the update
SELECT * FROM user:john VERSION d'2024-08-19T08:00:00Z';
[[{ id: user:john, name: 'John' }]]

Learn more

To learn more about using the SELECT statement to retrieve data from SurrealDB, check out this explainer video: