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.
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 interactions
: Retrieves all incoming relationships to Drake and aliases them as interactions.<->? AS connections
: Retrieves all relationships connected to Drake, regardless of direction, and aliases them as connections.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[ { a: 1, 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' } ] } } ]
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 null
for spouse_name
if the spouse
field is not present.
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.