Idioms in SurrealQL provide a powerful and flexible way to access and manipulate data within records using paths. They allow you to navigate through nested data structures, access fields, array elements, call methods, and perform complex queries with ease. Idioms are similar to expressions in other query languages that provide a path to data within documents or records.
An idiom is composed of a sequence of parts that define the path to a value within a record or data structure. Each part specifies how to navigate to the next piece of data. Idioms can be used in various parts of SurrealQL. The most common usecase is in data retrival queries such as SELECT
statements, but they can also be used in the WHERE
clause, SET
clause, and more.
An idiom is made up of one or more parts, each of which can be one of several types:
In this section, we’ll explore each part in detail with examples to help you understand how to use idioms in SurrealQL.
Since SurrealDB is, at its core, a document database, each record is stored on an underlying key-value store storage engine, with the ability to store arbitrary arrays, objects, and many other types of data. To access a field in an object, use a dot .
followed by the field name.
This is mostly helpful when accessing fields within a record, but can also be used to access fields within an array.
For example, using the CREATE
statement to add a record into the person
table:
QueryCREATE person CONTENT { name: "John Doe", age: 30, address: { city: "New York", country: "USA" } };
Response[ { address: { city: 'New York', country: 'USA' }, age: 30, id: person:g87bnds1gcgrnoj4p5q3, name: 'John Doe' } ]
To access the city
field within the address
object, you can use the following idiom:
QuerySELECT address.city FROM person;
Response[ { "address": { "city": "New York" } } ]
In this example, person.name
is an idiom that accesses the name
field of the person
record.
To access an element in an array by its index, use square brackets []
with the index inside. For example, let’s say we have a school
record with some student results.
QueryCREATE student SET results = [ { score: 76, date: "2017-06-18T08:00:00Z", name: "Algorithmics" }, { score: 83, date: "2018-03-21T08:00:00Z", name: "Concurrent Programming" }, { score: 69, date: "2018-09-17T08:00:00Z", name: "Advanced Computer Science 101" }, { score: 73, date: "2019-04-20T08:00:00Z", name: "Distributed Databases" }, ];
Response[ { id: student:urxaykt4qkbr8rs2o68j, results: [ { date: '2017-06-18T08:00:00Z', name: 'Algorithmics', score: 76 }, { date: '2018-03-21T08:00:00Z', name: 'Concurrent Programming', score: 83 }, { date: '2018-09-17T08:00:00Z', name: 'Advanced Computer Science 101', score: 69 }, { date: '2019-04-20T08:00:00Z', name: 'Distributed Databases', score: 73 } ] } ]
To access the first student in the results
array, you can use the following idiom:
SELECT results[0].score FROM student;
Response[ { "results": [ { "score": 76 } ] } ]
Here, results[0].score
accesses the score of the first student in the results
array.
To access all elements in an array or all fields in an object, use [*]
or .*
. This is useful when you want to access all the elements in an array or all the fields in an object.
SELECT results[*].score FROM student;
Response[ { results: { score: [ 76, 83, 69, 73 ] } } ]
This idiom selects all elements in the score
array.
.*
to return valuesAvailable since: v2.1.0
The .*
idiom in SurrealDB allows you to target all values in an object or all entries in an array. It can be used in various contexts such as querying, field definitions, and data manipulation. This section explains the behavior of .*
with practical examples.
When applied to an object, .*
returns an array containing all the values of the object’s properties.
{ a: 1, b: 2 }.*;
Result:
[1, 2]
In this example, { a: 1, b: 2 }.*
extracts the values 1
and 2
from the object and returns them as an array.
.*
You can define fields using .*
to specify constraints or types for all properties within an object field.
DEFINE FIELD obj ON test TYPE object; DEFINE FIELD obj.* ON test TYPE number;
Here, we define a field obj
of type object
on the test
table, and then specify that all properties within obj
(obj.*
) must be of type number
.
NoteAttempting to insert a non-number value into any property of
obj
will result in an error:
For example:
CREATE test:1 SET obj.a = 'a'; // Error Found 'a' for field `obj[*]`, with record `test:1`, but expected a number
.*
in Different ContextsDepending on where .*
is used, it can have different effects on the order of operations.
For example, if we want to return all the properties of the person:tobie
record, we can do the following:
SELECT * FROM ONLY person:tobie.*; -- This works SELECT * FROM ONLY (person:tobie.*); -- Equivalent to above SELECT * FROM ONLY { id: person:tobie, name: 'tobie' }; -- Equivalent to above
Output{ id: person:tobie, name: 'tobie' }
However, if we try to return all the properties of the person:tobie
record by enclosing .*
in parentheses, it will not work as expected. As the following example shows, it will return the value of the id
and name
fields instead.
(SELECT * FROM ONLY person:tobie).*;
Output[ person:tobie, 'tobie' ]
This can be thought of as an extension of the Field access idiom above. Because accessing a single field on a single object returns a single unstructured value, accessing all fields with .*
returns all of the values inside an array.
-- Returns 'tobie' (SELECT * FROM ONLY person:tobie).name; -- Returns [ person:tobie ] (SELECT * FROM ONLY person:tobie).id; -- Returns both name and id inside an array (SELECT * FROM ONLY person:tobie).*;
If the idiom path is on its own or enclosed in parentheses, it will return the record in full.
-- person:tobie.* means "expand all of the fields of person:tobie" SELECT * FROM ONLY person:tobie.*; (SELECT * FROM ONLY person:tobie.*); -- `.*` here means "go through each fields of the -- statement and return their values" -- (SELECT * FROM ONLY person:tobie).*;
Output{ id: person:tobie, name: 'tobie' }
Here is one more example showing the behaviour of .*
when applied to an object.
-- Turns the object into an array, returning -- [ person:tobie, 'tobie' ] { id: person:tobie, name: 'tobie' }.*; -- Thus equivalent to SELECT * FROM [ person:tobie, 'tobie' ] SELECT * FROM { id: person:tobie, name: 'tobie' }.*;
Output[ { id: person:tobie, name: 'tobie' }, 'tobie' ]
Addionally to access the last element of an array, use [$]
. Refereing to the student
record above, we can access the last element of the results
array using the following idiom:
SELECT results[$].score FROM student;
Response[ { results: { score: 73 } } ]
This idiom accesses the last element of the score
array.
Available since: v2.0.0
To call a method on the current data, use a dot .
followed by the method name and parentheses ()
with arguments. SurrealDB supports method chaining, so you can call multiple methods (functions) on the same data. Learn more about method chaining in the functions section.
For example, let’s create a new person
record
Create a new person recordCREATE person CONTENT { name: "John Doe", age: 30, address: { city: "New York", country: "USA" } };
Response[ { "person": { "name": "John Doe", "age": 30, "address": { "city": "New York", "country": "USA" } } } ]
To call the uppercase()
method on the name
field, you can use the following idiom:
SELECT name.uppercase() FROM person;
Response[ { "name": "JOHN DOE" } ]
In the example above, uppercase()
is a method called on person.name
to convert it to uppercase. Although this method is called as .uppercase()
it is actually the string::uppercase()
function that is called.
SurrealDB will automatically recognize that the idiom part .uppercase()
refers to the string::uppercase()
function and call this function when the query is executed. What this means is that the following two queries are equivilent:
Using method chainingSELECT name.uppercase() FROM person;
Using functionSELECT string::uppercase(name) FROM person;
To learn more about string method chaining in SurrealQL, see the string functions section.
SurrealDB can also be used in the context of graph databases, where data is stored and navigated using graph traversal idioms. The RELATE
statement is used to create relationships between records. This allows you to traverse related records efficiently without needing to pull data from multiple tables and merging that data together using SQL JOINs.
For example, let’s consider the following data:
Create a new planet, city, and explorer recordsCREATE planet:unknown_planet; CREATE city:el_dorado SET name = "El Dorado"; CREATE explorer:drake SET name = "Drake"; CREATE explorer:local_guide SET name = "Local Guide"; RELATE explorer:drake->discovered->planet:unknown_planet; RELATE explorer:drake->visited->city:el_dorado; RELATE explorer:local_guide->assisted->explorer:drake;
Retrieve all relationships from DrakeSELECT *, ->? AS actions, <-? AS was, <->? AS involved_in FROM explorer:drake;
Response[ { actions: [ discovered:sh9zbsz5u705cxv6qgoi, visited:hmtttiqqfa4mt9is1a7j ], involved_in: [ assisted:1pv8k3p1wpuf0guf5bvm, discovered:sh9zbsz5u705cxv6qgoi, visited:hmtttiqqfa4mt9is1a7j ], id: explorer:drake, was: [ assisted:1pv8k3p1wpuf0guf5bvm ], name: 'Drake' } ]
Explanation:
*
: Selects all fields of explorer:drake
.->? AS actions
: Retrieves all outgoing relationships from Drake and aliases them as actions.<-? AS was
: Retrieves all incoming relationships to Drake and aliases them as was.<->? AS involved_in
: Retrieves all relationships connected to Drake, regardless of direction, and aliases them as involved_in.Available since: v2.0.0
When working with nested data, you can destructure objects using the .
and { ... }
idioms.
For example,
Create a new person recordCREATE person:1 SET name = 'John', age = 21, obj = { a: 1, b: 2, c: { d: 3, e: 4, f: 5 } };
Response[ { age: 21, id: person:1, name: 'John', obj: { a: 1, b: 2, c: { d: 3, e: 4, f: 5 } } } ]
SELECT obj.{ a, c.{ e, f } } FROM person;
Response[ { obj: { a: 1, c: { e: 4, f: 5 } } } ]
You can also OMIT fields that you don’t want to destructure using the OMIT
clause.
SELECT * OMIT obj.c.{ d, f } FROM person;
Response[ { age: 21, id: person:1, name: 'John', obj: { a: 1, b: 2, c: { e: 4 } } } ]
Extending the example in the Graph Navigation section, we can use the ->
idiom to navigate through the graph and destructure the city
field.
SELECT ->visited->city.{name, id} FROM explorer:drake;
Response[ { "->visited": { "->city": [ { id: city:el_dorado, name: 'El Dorado' } ] } } ]
The keyword AS
is necessary inside SELECT
statements when using an alias (a new name for a field).
LET $town = { location: (50.0, -5.4), population: 500 }; SELECT location, population AS num_people FROM ONLY $town;
Output{ location: (50, -5.4), num_people: 500 }
However, as destructuring involves the creation of a new object, no AS
keyword is needed. Instead, only the names of the fields are needed. Aliasing is done by choosing a new name, a :
and the path to the value.
LET $town = { location: (50.0, -5.4), population: 500 }; RETURN $town.{ location, num_people: population };
Conceptually, this is somewhat close to a RETURN
statement.
LET $town = { location: (50.0, -5.4), population: 500 }; RETURN { location: $town.location, num_people: $town.population, };
However, note that destructuring only involves manipulating the object in question, and no parameters or extra fields can be added.
LET $town = { location: (50.0, -5.4), population: 500 }; LET $mayor = person:billy; RETURN { location: $town.location, num_people: $town.population, mayor: $mayor, best_dog: dog:mr_bark }; -- Final lines won't work as neither -- are fields of the $town object $town.{ location, num_people: population, -- mayor: $mayor, -- best_dog: dog:mr_bark };
Available since: v2.1.0
Since version 2.1, the current record in a SELECT
query can be accessed and destructured using the @
operator.
CREATE star:sun SET name = "The Sun"; CREATE planet:earth SET name = "Earth"; RELATE planet:earth->orbits->star:sun; -- Regular SELECT query SELECT name, id, ->orbits->star AS orbits FROM planet; -- SELECT query using `@` and destructuring SELECT @.{ name, id, orbits: ->orbits->star } FROM planet;
While the difference between the two methods is often cosmetic - aside from the note on aliases mentioned just above - using @
to access the current record does lead to a different style of query that may be preferable. While a regular SELECT
query first returns an array of results that can then be operated on, a SELECT
query that uses @
to access the current record can perform these operations first.
-- Use the .values() method to turn each record into -- an array of values, then return all inside an array SELECT @.{ name, id, orbits: ->orbits->star }.values() FROM planet; -- Grab all records first, then use .map() to convert -- each one into an array of values (SELECT name, id, ->orbits->star AS orbits FROM planet) .map(|$obj| $obj.values());
Most importantly, however, the @
operator is often necessary when using recursive paths.
Available since: v2.0.0
The ?
operator is used to indicate that a part is optional (it may not exist) it also allows you to safely access nested data without having to check if the nested data exists and exit an idiom path early when the result is NONE.
SELECT person.spouse?.name FROM person;
This idiom safely accesses person.spouse.name
if spouse
exists; otherwise, it returns NONE
.
If some person
records have a spouse
field and others do not:
SELECT name, spouse?.name AS spouse_name FROM person;
This idiom will return NONE
for spouse_name
if the spouse
field is not present.
Available since: v2.1.0
A recursive path allows record link or graph traversal down to a specified depth, as opposed to manually putting together a query to navigate down each level.
Using recursive graph traversal can be thought of as the equivalent of “show me all the third-generation descendants of Mr. Brown” as opposed to “show me the children and children’s children and children’s children’s children of Mr. Brown”.
The following shows a recursive query that returns the names of people known by records that the record person:tobie
knows.
-- Get all names of people second to Tobie person:tobie.{2}(->friends_with->person).name;
As the syntax of recursive queries tends to be complex to the untrained eye, this section will explain them in order of difficulty, beginning with what queries were necessary before recursive paths were added in SurrealDB version 2.1.
Take the following example that creates one planet, two countries, two states/provinces in each of these countries, and two cities in each of those states/provinces. The CREATE
statements are followed by UPDATE
statements to set record links between them, and RELATE
to create bidirectional graph relations between them.
CREATE // One planet planet:earth, // Two countries country:us, country:canada, // Four states/provinces state:california, state:texas, province:ontario, province:bc, // Eight cities city:los_angeles, city:san_francisco, city:houston, city:dallas, city:vancouver, city:victoria, city:toronto, city:ottawa // Give them each names like 'earth', 'us', 'bc', etc. SET name = id.id(); // Record and graph links from planet to country UPDATE planet:earth SET next = [country:us, country:canada]; RELATE planet:earth ->has-> [country:us, country:canada]; // Record and graph links from country to state/province UPDATE country:us SET next = [state:california, state:texas]; UPDATE country:canada SET next = [province:ontario, province:bc]; RELATE country:us ->has-> [state:california, state:texas]; RELATE country:canada ->has-> [province:bc, province:ontario]; // Record and graph links from state/province to city UPDATE state:california SET next = [city:los_angeles, city:san_francisco]; UPDATE state:texas SET next = [city:houston, city:dallas]; UPDATE province:ontario SET next = [city:toronto, city:ottawa]; UPDATE province:bc SET next = [city:vancouver, city:victoria]; RELATE state:california ->has-> [city:los_angeles, city:san_francisco]; RELATE state:texas ->has-> [city:houston, city:dallas]; RELATE province:bc ->has-> [city:vancouver, city:victoria]; RELATE province:ontario ->has-> [city:toronto, city:ottawa];
Before version 2.1.0
, traversing each of these paths could only be done manually, requiring a good deal of typing and knowing the exact depth to traverse.
Here is an example using record links:
SELECT next AS countries, next.next AS states_provinces, next.next.next AS cities FROM planet:earth;
Response[ { cities: [ [ [ city:los_angeles, city:san_francisco ], [ city:houston, city:dallas ] ], [ [ city:toronto, city:ottawa ], [ city:vancouver, city:victoria ] ] ], countries: [ country:us, country:canada ], states_provinces: [ [ state:california, state:texas ], [ province:ontario, province:bc ] ] } ]
And here is an example using graph links.
SELECT -- Show all `country` records located at `out` ->has->country AS countries, -- Show all `province` or `state` records located at `out` ->has->country->has->(province, state) AS state_provinces, -- Or use (?) to show any type of record located at `out` ->has->(?)->has->(?)->has->(?) AS cities FROM planet:earth;
Output[ { cities: [ city:toronto, city:ottawa, city:vancouver, city:victoria, city:dallas, city:houston, city:los_angeles, city:san_francisco ], countries: [ country:canada, country:us ], state_provinces: [ province:ontario, province:bc, state:texas, state:california ] } ]
Using a recursive path allows you to instead set the number of steps to follow instead of manually typing. A recursive path is made by isolating {}
braces in between two dots, inside which the number of steps is indicated.
-- Two steps down the record links at the `next` field planet:earth.{2}.next; -- Two steps down the `has` graph relation planet:earth.{2}->has->(?);
Output[ state:california, state:texas, province:ontario, province:bc ]
The number of steps can be any integer from 1 to 256.
-- 'Found 0 for bound but expected at least 1.' planet:earth.{0}->has->(?); -- 'Found 500 for bound but expected 256 at most.' planet:earth.{500}->has->(?);
A range can be inserted into the braces to indicate a desired minimum and maximum depth.
-- Returns [] because no 4th-level relations exist planet:earth.{4}->has->(?); -- Returns `city` records located at depth 3 planet:earth.{1..4}->has->(?); -- Open-ended range: also returns `city` records at depth 3 planet:earth.{..}->has->(?);
Output[ city:toronto, city:ottawa, city:vancouver, city:victoria, city:dallas, city:houston, city:los_angeles, city:san_francisco ]
Parentheses can be added to a recursive query. To explain their use, consider the following example that attempts to traverse up to a depth of 3 and return the name
of the records at that level.
planet:earth.{1..3}->has->(?).name;
Unfortunately, the output shows that the query stopped at a depth of one. This is because the query is instructing the database to recurse the entire ->has->(?).name
path between 1 and 3 times, but after the first recursion it has reached a string. And a string on its own is of no use in a ->has->(?)
graph query which expects a record ID.
Output[ 'canada', 'us' ]
In fact, the above query is equivalent to the following statement which encloses ->has->(?).name
in parentheses.
planet:earth.{1..3}(->has->(?).name);
To make the query work, we can shrink the area enclosed in the parentheses to ->has->(?)
, isolating the part to recurse before moving on to .name
. It will repeat as many times as instructed and only then move on to the name
field.
planet:earth.{1..3}(->has->(?)).name;
Output[ 'toronto', 'ottawa', 'vancouver', 'victoria', 'dallas', 'houston', 'los_angeles', 'san_francisco' ]
The syntax for the query above can be broken down as follows.
-- starting point planet:earth -- desired depth .{1..3} -- instructions for current document (->has->(?)) -- leftover idiom path .{name, id};
@
to refer to the current recordThe @
symbol is used in recursive queries to refer to the current document. This is needed in recursive SELECT
queries, as without it there is no way to know the context.
Unparsable queries-- Parse error: what is the `.` referring to? -- DB: "Call recursive query on a `planet`? Its `name` field? Something else?" SELECT .{1..3}(->has->(?)) FROM planet; -- A similar query that can't be parsed -- DB: "Call .len() on what?" SELECT .len() FROM planet;
Adding @
allows the parser to know that the current planet
record is the starting point for the rest of the query.
Parsable queries-- Will now call `.{1..3}(has->(?))` on every planet record it finds SELECT @.{1..3}(->has->(?)) AS cities FROM planet; -- Will now call `.len()` on every `name` field it finds SELECT name.len() AS length FROM planet;
{}
and .@
to combine resultsInside the structure of a recursive graph query, the @
symbol is used in the form of .@
at the end of a path to inform the database that this is the path to be repeated during the recursion. This allows not just the fields on the final depth of the query to be returned, but each one along the way as well.
planet:earth .{1..2} .{ name, id, -- Query with ->has->(?) on the current record contains: ->has->(?).@ };
Output{ contains: [ { contains: [ province:ontario, province:bc ], id: country:canada, name: 'canada' }, { contains: [ state:texas, state:california ], id: country:us, name: 'us' } ], id: planet:earth, name: 'earth' }
The following two rules of thumb are a good way to understand how the syntax inside the structure of the query.
.@
is used as the gateway to the next depth.To see this visually, here is the unfolded output of the query above. The name
and id
fields appear at each point, while contains
is used to move on to the next depth.
-- Original query planet:earth.{1..2}.{ name, id, contains: ->has->(?).@ }; -- Unfolds to: planet:earth .{ name, id, contains: ->has->(?).{ name, id, contains: ->has->(?) } };
Similarly, only one .@
can be present inside such a query, as this is the path that is used to follow the recursive query until the end.
planet:earth .{1..2} .{ name, id, -- Query with ->has->(?) on the current record contains: ->has->(?).@, contains2: ->has->(?).@ };
'Tried to use a `@` repeat recurse symbol in a position where it is not supported'
Here are some more simple examples of recursive queries and notes on the output they generate.
INSERT INTO person [ { id: person:tobie, name: 'Tobie', friends: [person:jaime, person:micha] }, { id: person:jaime, name: 'Jaime', friends: [person:mary] }, { id: person:micha, name: 'Micha', friends: [person:john] }, { id: person:john, name: 'John' }, { id: person:mary, name: 'Mary' }, { id: person:tim, name: 'Tim' }, ]; INSERT RELATION INTO knows [ { id: knows:1, in: person:tobie, out: person:jaime }, { id: knows:2, in: person:tobie, out: person:micha }, { id: knows:3, in: person:micha, out: person:john }, { id: knows:4, in: person:jaime, out: person:mary }, { id: knows:5, in: person:mary, out: person:tim }, ]; -- Any depth person:tobie.{..}(->knows->person).name; -- Minimum 2, maximum 5 iterations of recursion (or either) person:tobie.{2..6}(->knows->person).name; person:tobie.{2..}(->knows->person).name; person:tobie.{..6}(->knows->person).name; -- Generate complex recursive tree structures: -- Fetches connections up to 3 levels deep, -- collecting their name, id, and connections along the way -- 3 levels, because the first iteration is used to collect -- the details for person:tobie person:tobie.{..4}.{ id, name, connections: ->knows->person.@ }; -- @ is a shortcut to the current document, and acts as a shorthand to start an idiom path. -- The "." can optionally be omitted SELECT @{1..4}(->knows->person).name AS names_2nds FROM person; -- Recursive idioms work with any idiom parts, not limited to graphs -- Here, we recursively fetch friends and then collect their names person:tobie.{1..5}(.friend).name;
Recursive queries follow a few rules to determine how far to traverse and what to return. They are:
NONE
, NULL
, and arrays which are empty or contain only NONE
and/or NULL
are considered a dead end.NONE
.Recursive syntax is not just useful in creating recursive queries, but parsing them as well. Take the following example that creates some person
records, gives each of them two friends, and then traverses the friends_with
graph for the first person
records to find its friends, friends of friends, and friends of friends of friends. Since every level except the last contains another connections
field, adding a .{some_number}.connections
to a RETURN
statement is all that is needed to drill down to a certain depth.
CREATE |person:1..20| SET name = id.id() RETURN NONE; FOR $person IN SELECT * FROM person { LET $friends = (SELECT * FROM person WHERE id != $person.id ORDER BY rand() LIMIT 2); RELATE $person->friends_with->$friends; }; LET $third_degree = person:1.{..3}.{ id, connections: ->friends_with->person.@ }; // Object containing array of arrays of arrays of 'person' RETURN $third_degree; // All connections: an array of arrays of arrays of 'person' RETURN $third_degree.connections; // Secondary connections: an array of arrays of 'person' RETURN $third_degree.{2}.connections; // Tertiary connections: an array of 'person' RETURN $third_degree.{3}.connections; // Tertiary connections with aliased fields and original 'person' info RETURN $third_degree.{ original_person: id, third_degree_friends: connections.{2}.connections };
Possible output of the final query:
Output for third_degree_friends query{ original_person: person:1, third_degree_friends: [ person:13, person:3, person:14, person:10, person:8, person:3, person:3, person:14 ] }
Idioms can combine multiple parts to navigate complex data structures seamlessly.
Suppose we have the following data:
Create a new person recordCREATE person:5 CONTENT { name: "Eve", friends: [ { id: "person:6", name: "Frank", age: 25 }, { id: "person:7", name: "Grace", age: 19 }, { id: "person:8", name: "Heidi", age: 17 } ] };
Response{ "id": "person:5", "name": "Eve", "friends": [ { "id": "person:6", "name": "Frank", "age": 25 }, { "id": "person:7", "name": "Grace", "age": 19 }, { "id": "person:8", "name": "Heidi", "age": 17 } ] }
To get the names of friends who are over 18:
SELECT friends[WHERE age > 18].name FROM person WHERE id = r'person:5';
Response[ { friends: { name: [ 'Frank', 'Grace' ] } } ]
?
) to handle NONE
or missing data gracefully.WHERE
clauses for better performance.Idioms in SurrealQL are a powerful tool for navigating and manipulating data within your database. By understanding and effectively using idiom parts, you can write expressive and efficient queries that handle complex data structures with ease. Whether you’re accessing nested fields, filtering arrays, or traversing graph relationships, idioms provide the flexibility you need to interact with your data seamlessly.