Idioms

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:

Field: Access a field by name.
Index: Access an element of an array by its index.
All: Access all elements or fields.
Last: Access the last element of an array.
Where: Filter elements based on a condition.
Method: Call a method on the current data.
Graph: Navigate through graph relationships.
Destructure: Destructure nested objects.
Optional: Indicate that the following part is optional.
Recurse: Recursively traverse paths such as graph and record links.

In this section, we’ll explore each part in detail with examples to help you understand how to use idioms in SurrealQL.

Field Access

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:

Query
CREATE 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:

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

Index Access

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.

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

All Elements

To access all elements in an array or all fields in an object, use .*. This is useful when you want to access all the elements in an array or all the fields in an object.

SELECT results.* FROM student;

Response
[
	{
		results: {
			score: [
				76,
				83,
				69,
				73
			]
		}
	}
]

This idiom selects all elements in the score array.

The operator [*] can also be used as an alias of .*, and is often seen in definitions and error messages.

DEFINE FIELD friends ON TABLE person TYPE array<record<person>>;
INFO FOR TABLE person;

The output for INFO FOR TABLE person includes an automatically generated definition for friends[*], namely every item inside the friends field.

{
	events: {},
	fields: {
		friends: 'DEFINE FIELD friends ON person TYPE array<record<person>> PERMISSIONS FULL',
		"friends[*]": 'DEFINE FIELD friends[*] ON person TYPE record<person> PERMISSIONS FULL'
	},
	indexes: {},
	lives: {},
	tables: {}
}

Using `.*` to return values

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

Accessing all values in an object

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.

Defining Fields with `.*`

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.

Note
Attempting 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

Using `.*` in Different Contexts

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

Last Element

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.

Method chaining

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 record
CREATE 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 chaining
SELECT name.uppercase() FROM person;

Using function
SELECT 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 records
CREATE 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 Drake
SELECT 
    *,
    ->? 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.

Destructuring

Available since: v2.0.0

When working with nested data, you can destructure objects using the . and { ... } idioms.

For example,

Create a new person record
CREATE 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'
				}
			]
		}
	}
]

Using aliases when destructuring

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

Destructuring the current item in a SELECT query

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.

Optional Parts

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.

Using Optional Parts

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.

Recursive paths

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.

Overview

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
		]
	}
]

Basics of recursive paths

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
]

Using () to provide instructions at each depth

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

Using `@` to refer to the current record

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

Using `{}` and `.@` to combine results

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

The individual fields inside a recursive query are simply populated at each point,
The field with .@ 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;

Behaviour of recursive queries

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.
An iteration with the same value as the previous one is also considered a dead end.
If an iteration with a dead end does not reach the minimum depth, it returns NONE.
If it has already passed the minimum depth, it returns the last valid value.
During each iteration, if it encounters an array value, all dead end values are automatically filtered out, ensuring no empty paths are included.

Filtering recursive fields

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
	]
}

Path and unique node collection, shortest path

Available since: v2.2.0

SurrealDB has a number of built-in algorithms that allow recursive queries to collect all paths, all unique nodes, and to find the shortest path to a record. These can be used by adding the following keywords to the part of the recursive syntax that specifies the depth to recurse:

{..+path}: used to collect all walked paths.
{..+collect}: used to collect all unique nodes walked.
{..+shortest=record:id}: used to find the shortest path to a specified record id, such as person:tobie or person:one.

The originating (first) record is excluded from these paths by default. However, it can be included by adding +inclusive to the syntax above.

{..+path+inclusive}
{..+collect+inclusive}
{..+shortest=record:id+inclusive}

To demonstrate the output of these three algorithms, take the following example showing a small network of friends. The network begins with person:you, followed by two friends (person:friend1, person:friend2), then three acquaintances known by these friends (person:acquaintance1, person:acquaintance2, person:acquaintance3), and finally a movie star (person:star) who is known by only one of the acquaintances.

CREATE 
	person:you, 
	person:friend1, person:friend2, 
	person:acquaintance1, person:acquaintance2, person:acquaintance3, 
	person:star
-- Give each of them a name like 'you', 'friend1', etc.
SET name = id.id();

-- You have two friends
RELATE person:you->knows->[person:friend1, person:friend2];
-- The first friend is shy and only knows one other person
RELATE person:friend1->knows->person:friend2;
-- The second friend is very social and knows many people you barely know
RELATE person:friend2->knows->[person:acquaintance1, person:acquaintance2, person:acquaintance3];
-- One of those people knows the movie star
RELATE person:acquaintance3->knows->person:star;

This representation of this small network of friends allows us to visualize the issues that these three algorithms solve. Using +path will output all of the possible paths from person:you, +collect will collect all of the records in this network, and +shortest=person:star will find the shortest path.

‎
								  ┌───────►  person:friend1  
     ┌───►person:acquaintance1    │                                                                    
     │                │           │                                                
     │                │           ┼───►person:acquaintance2    person:star   
person:you            │           │                                 ▲        
     │                ▼           │                                 │        
     └────────► person:friend2────┤                                 │        
                                  └───►person:acquaintance3─────────┘

After specifying an algorithm to use, such as {..+path}, add the path that should be followed, in this case ->knows->person.

+path

Adding +path will output all of the possible paths starting from person:you.

person:you.{..+path}->knows->person;

Output
[
	[
		person:friend2,
		person:acquaintance2
	],
	[
		person:friend2,
		person:acquaintance1
	],
	[
		person:friend1,
		person:friend2,
		person:acquaintance2
	],
	[
		person:friend1,
		person:friend2,
		person:acquaintance1
	],
	[
		person:friend2,
		person:acquaintance3,
		person:star
	],
	[
		person:friend1,
		person:friend2,
		person:acquaintance3,
		person:star
	]
]

+shortest

As the output of the previous example is fairly short, we can see that there are two ways to get from person:one to the movie star at person:star, one of which is one step shorter than the other.

To get the database to find the shortest path instead, change the algorithm to +shortest=person:star.

person:you.{..+shortest=person:star}->knows->person;

Output
[
	person:friend2,
	person:acquaintance3,
	person:star
]

The part after +shortest can also take a parameter if it is a record ID. The following example will return the same result as the previous one.

LET $you = SELECT VALUE id FROM ONLY person WHERE name = 'you' LIMIT 1;
LET $star = SELECT VALUE id FROM ONLY person WHERE name = 'star' LIMIT 1;
$you.{..+shortest=$star}->knows->person;

+collect

Using +collect will collect all of the unique collected records. As this collection is created by moving recursively one level at a time, the output will show the closest connections first and least close connections at the end.

person:you.{..+collect}->knows->person;

Output
[
	person:friend1,
	person:friend2,
	person:acquaintance2,
	person:acquaintance1,
	person:acquaintance3,
	person:star
]

+inclusive

Adding +inclusive will show the same output, except that the original person:one record will also be present.

person:you.{..+shortest=person:star+inclusive}->knows->person;
person:you.{..+collect+inclusive}->knows->person;

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

[
	person:you,
	person:friend2,
	person:acquaintance3,
	person:star
]

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

[
	person:you,
	person:friend1,
	person:friend2,
	person:acquaintance2,
	person:acquaintance1,
	person:acquaintance3,
	person:star
]

Other notes

The unbounded syntax .. can be replaced with a bounded range to ensure that the recursive query only goes down to a certain depth. For example, using ..2 with +collect will show all first- and second-degree relations starting from person:you:

person:you.{..2+collect}->knows->person;

All first- and second-degree relations
[
	person:friend1,
	person:friend2,
	person:acquaintance2,
	person:acquaintance1,
	person:acquaintance3
]

Doing the same with +shortest=person:star will return an empty array, because there is no path from person:you to person:star that only requires two hops.

person:you.{..2+shortest=person:star}->knows->person;

Output
[]

As shown in a previous section, parentheses can be used to show which path should be repeated during the recursion. After the path inside the parentheses, the destructuring operator, methods and so on can be used to modify the output. The query can also be written over multiple lines if desired.

-- Start with you
person:you
-- Get the shortest path
	.{..+shortest=person:star+inclusive}
-- by following ->knows->person
	(->knows->person)
-- then grab the names
	.name
-- and capitalize each one
	.map(|$n| $n.uppercase());

Output
[
	'YOU',
	'FRIEND2',
	'ACQUAINTANCE3',
	'STAR'
]

Do not use `.@` with algorithms

As these three methods use their own algorithms to follow a path, any attempt to construct your own path using .@ will result in an error. For example, choosing +path along with a field connections: ->knows->person.@ will return an error because +path on its own will use its own recursive planner to output every possible path as an array of arrays, while ->knowns->person.@ is an instruction to put together arrays of each record and the next result from the ->knows->person path at any possible depth.

person:you.{..+path}.{
    id,
    connections: ->knows->person.@
};

'Can not construct a recursion plan when an instruction is provided'

Here is the output of both of these queries at a single depth to show the difference in output.

person:you.{..1}.{
    id,
    connections: ->knows->person.@
};

person:you.{..1+path}->knows->person;

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

{
	connections: [
		person:friend2,
		person:friend1
	],
	id: person:you
}

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

[
	[
		person:friend2
	],
	[
		person:friend1
	]
]

Example using record links

As is the case with other recursive queries, these three algorithms can be used in the same way with any other path that can be repeated, such as record links. The following example shows the same network of friends as the one above, except that it uses record links instead of graph queries. To traverse these paths, a simple .knows is all that is required.

CREATE person:you SET knows = [person:friend1, person:friend2];
CREATE person:friend1 SET knows = [person:friend2];
CREATE person:friend2 SET knows = [person:acquaintance1, person:acquaintance2, person:acquaintance3];
CREATE person:acquaintance1, person:acquaintance2, person:star;
CREATE person:acquaintance3 SET knows = [person:star];

person:you.{..+shortest=person:star}.knows;
person:you.{..+path}.knows;
person:you.{..+collect}.knows;

Combining Idiom Parts

Idioms can combine multiple parts to navigate complex data structures seamlessly.

Suppose we have the following data:

Create a new person record
CREATE 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'
			]
		}
	}
]

Notes on Idioms

Chaining: Idioms can be chained to traverse deeply nested structures.
Performance: Be mindful of performance when using complex idioms; indexing fields can help.
NONE Safety: Use optional parts (?) to handle NONE or missing data gracefully.
Methods: Leverage built-in methods for data manipulation within idioms.
Type Casting: Use type casting if necessary to ensure data is in the correct format.

Best Practices

Use Destructuring: When selecting multiple fields, destructuring improves readability.
Limit Optional Parts: Use optional parts judiciously to avoid masking data issues.
Validate Data: Ensure data conforms to expected structures, especially when dealing with optional fields.
Index Fields: Index fields that are frequently accessed or used in WHERE clauses for better performance.

Summary

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.

Edit this page on GitHub

Geometries Literals

SurrealQL

Idioms

Field Access

Index Access

All Elements

Using .* to return values

Accessing all values in an object

Defining Fields with .*

Using .* in Different Contexts

Last Element

Method chaining

Graph Navigation

Destructuring

Using aliases when destructuring

Destructuring the current item in a SELECT query

Optional Parts

Using Optional Parts

Recursive paths

Overview

Basics of recursive paths

Using () to provide instructions at each depth

Using @ to refer to the current record

Using {} and .@ to combine results

Behaviour of recursive queries

Filtering recursive fields

Path and unique node collection, shortest path

+path

+shortest

+collect

+inclusive

Other notes

Do not use .@ with algorithms

Example using record links

Combining Idiom Parts

Notes on Idioms

Best Practices

Summary

On this page

Using `.*` to return values

Defining Fields with `.*`

Using `.*` in Different Contexts

Using `@` to refer to the current record

Using `{}` and `.@` to combine results

Do not use `.@` with algorithms