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.

Parts of an Idiom

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.

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 [*] 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.

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.

Graph Navigation

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

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
[
	{
		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'
				}
			]
		}
	}
]

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 null for spouse_name if the spouse field is not present.

Combining Idiom Parts

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

Complex Example

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.