Skip to main content

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 [ AT ] @field ... ]
	[ GROUP [ BY ] @fields ... ]
	[ ORDER [ BY ]
		@fields [
			RAND()
			| COLLATE
			| NUMERIC
		] [ ASC | DESC ] ...
	]
	[ LIMIT [ BY ] @limit ]
	[ START [ AT ] @start ]
	[ FETCH @fields ... ]
	[ TIMEOUT @duration ]
	[ PARALLEL ]
	[ 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.

-- 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;

-- Alias/rename fields
SELECT name AS user_name, address FROM person;

-- 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:john;

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 * 2 ) + 30 ) 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
-- You can see the graph 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;

Using parameters​

Parameters can be used like variables to store a value which can then be used in a subsequent query. SurrealDB also uses a few 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;

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'];

Skip certain fields using the OMIT clause​

Sometimes, especially with tables containing numerous columns, users might wish there was an easier way to select all columns except a few specific ones, with the OMIT clause you can omit certain fields from records as they are output.

-- Omit the password field and security field in the options object
CREATE person:tobie SET name = 'Tobie', password = '123456', opts.security = 'secure';
CREATE person:jaime SET name = 'Jaime', password = 'asdfgh', opts.security = 'secure';
SELECT * OMIT password, opts.security FROM person;
SELECT * FROM person;

Select multiple targets using the FROM clause​

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

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

-- This command 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);

-- This command 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;

-- This command 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];

-- This command 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, the SurrealDB SELECT queries support 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.

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

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

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;

-- Group results with aggregate functions
SELECT count() AS total, math::sum(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;

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

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;

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 in parallel. This can significantly improve the performance of the statement, but it is important to note that the statement will not be processed in a transactional manner, and so the results may not be consistent.

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

-- Returns the execution plan
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN;

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

[
		{
				detail: {
						table: 'person'
				},
				operation: 'Iterate Table'
		}
]
-- Returns the execution plan with the number of fetched rows
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN FULL;

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

[
		{
				detail: {
						plan: {
								index: 'email',
								operator: '=',
								value: 'tobie@surrealdb.com'
						},
						table: 'person'
				},
				operation: 'Iterate Index'
		},
		{
				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 NOINDEX forces the query planner to use the table iterator.
  • WITH INDEX @indexes ... restricts the query planner to using only the specified index(es)
-- 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 INDEX idx_name WHERE job='engineer' AND genre = '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;

Learn more​

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

What's on your mind?

Create GitHub Issue

Quickest way to alert
our team of a problem.