Chapter 16: Keeping knowledge free

Time elapsed: 35y

Your plan to recreate the Minitel is going well, especially in your part of the world where people are used to working with technology. The great powers of Europe wanted the same access, and you were willing to grant it. You put an offer together that provides a number of Minitels for government usage, just as they asked. However, there were conditions - tough ones. One was that they must allow Landevin to put together a team that can set up Minitels wherever they like. And everyone, even the poorest citizen, must be able to access it for free. Any government that interferes with the team will lose all access to the network.

‎

The last condition was the hardest: allowing Landevin’s team to visit anywhere unannounced, including courts and jails. How they hated that one! And they all refused at first, except San Marino. The other countries were shocked, calling it “unworthy of an independent country”. Then a few months went by and San Marino began to gain a huge technological advantage over them. They all jumped aboard after that.

‎

You know that they will eventually figure out the technology for themselves, but for the moment there is no danger of this happening. The knowledge is all inside your database, and all they can do is display the results on the screen which they write down on paper for their own use later. You smile as you remember doing the exact same thing so many years ago when the printer first ran out of ink…

A full 35 years have passed since Aeon began the project to restore civilization, and it seems to be getting closer to succeeding. There isn’t much time left! In this small amount of time remaining to us, let’s focus on advanced queries and any other concepts that we haven’t encountered yet over the next two chapters to ensure that we are as fluent in SurrealQL as possible.

Futures

SurrealDB has an interesting type called a future that represents some value that doesn’t exist until you make a query. As such, it lives as an expression to calculate, instead of a value.

To get into a future frame of mind, let’s look at a simple query that has a similar behaviour: a comet with a timestamp of the date when it was discovered.

CREATE sky_object SET
	type = "comet",
	first_seen = time::now();

This type for this sky_object will always be “comet”, but first_seen depends on when the function is evaluated.

Similarly, when querying some sky_object records later on, you might want to add a field that returns the time since the object in the sky was discovered. Since first_seen is a datetime, we can just subtract it from the output of time::now().

SELECT *,
	time::now() - first_seen AS known_for
FROM sky_object;

The output will depend on how long the object has been in the database for - five minutes, five days, or any other duration.

[
	{
		first_seen: d'2024-07-09T00:48:14.096Z',
		id: sky_object:bpn5sznab7fmrkmixcrb,
		known_for: 5m51s851ms,
		type: 'comet'
	}
]

A future lets you define this sort of expression on a table itself, so that the field is automatically calculated for you instead of having to put together a SELECT expression to do it. To create a future, first cast with <future>, and then use {} to open up a scope inside in which you add the expression to be evaluated.

Here is how we would turn the known_for alias in the query above into a known_for field that holds a future:

DEFINE FIELD first_seen
	ON TABLE sky_object
	TYPE datetime;
DEFINE FIELD known_for
	ON TABLE sky_object
	VALUE <future> { time::now() - first_seen };

And now we only need to SELECT * to get the same information as the query above! Try highlighting just the final SELECT * FROM sky_object and running it over and over again to see the first_seen duration increase every time.

CREATE sky_object
	SET type = "comet",
	first_seen = time::now();
SELECT * FROM sky_object;

Having a field on a table that can be calculated in this way instead of stored as a set value opens up quite a few possibilities. One interesting use for a future that SurrealDB users have found is the ability to use it in place of a record ID or a RELATE statement to join records together!

Using futures to simulate a relation

Do you remember the properties field on the person records from the story of the Enchanted Knights that we worked with from chapters 5 to 8? We used this field to represent the castle owned by the Nobleman. Here is what the CREATE statements looked like to create him and his castle:

CREATE person:the_nobleman SET
    name = "The Nobleman",
    class = "Count",
    money = 50;

CREATE building:old_castle SET
  name = "Old castle",
  kind = "castle";

UPDATE person:the_nobleman SET
	properties = [building:old_castle];

A record ID link goes in a single direction, so a SELECT * FROM building at this point would only show the building record on its own, with no link back to the person. We could have used RELATE to create a bidirectional link instead, but we let’s see how we could use a future to create something similar.

To make sure that our logic is sound, let’s first try using a query to find all the person records whose properties are linked to the building in question. To do this, we can add a subquery that selects all the person records in which the id of the building matches. Since this second select is inside a subquery, we can use $parent to compare against the building records (from the main query) as we learned to do in Chapter 9.

SELECT *,
	(SELECT * FROM person WHERE $parent.id IN properties) AS owners
FROM building;

The output now shows the Nobleman as the building’s owner.

[
	{
		id: building:old_castle,
		kind: 'castle',
		name: 'Old castle',
		owners: [
			{
				class: 'Count',
				id: person:the_nobleman,
				money: 50,
				name: 'The Nobleman',
				properties: [
					building:old_castle
				]
			}
		]
	}
]

Now let’s put this into the schema using a future. This can be done using $this as well, which (as we saw once in Chapter 5) inside a DEFINE FIELD statement refers to the parent table, while$value is the parameter which refers to the field itself.

We can demonstrate this by adding a field called all_data that simply contains $this. Let’s put it on a table called cat just for experimentation.

DEFINE FIELD all_data ON TABLE cat VALUE $this;
CREATE cat SET name = "Mrs. Meow";
SELECT * FROM cat;

As the output shows, all_data is simply the record itself.

[
	{
		all_data: {
			id: cat:izsz97r6eobd0zkfskk5,
			name: 'Mrs. Meow'
		},
		id: cat:izsz97r6eobd0zkfskk5,
		name: 'Mrs. Meow'
	}
]

Knowing this, we can now create the expression to determine the owner. It will be SELECT * FROM person WHERE $parent.id IN properties, using $parent.id instead of $this.id because once the SELECT expression begins, $this is now a building instead of a person.

Let’s first define the field without making it a future to see what happens.

DEFINE FIELD owners
	ON TABLE building
	VALUE (SELECT * FROM person WHERE $parent.id IN properties);

CREATE person:the_nobleman SET
    name = "The Nobleman",
    class = "Count",
    money = 50;

CREATE building:old_castle SET
  name = "Old castle",
  kind = "castle";

UPDATE person:the_nobleman SET
	properties = [building:old_castle];

SELECT * FROM building;

The output shows [] for owners, because the building was created at the time that no person records linked to it.

[
	{
		id: building:old_castle,
		kind: 'castle',
		name: 'Old castle',
		owners: []
	}
]

If we had created the building record after the person was linked to it, then the owner would have shown up instead of []. But what if we were to add the Nobleman’s wife as well, who also owns the same castle? We would have to keep an eye on order of creation there as well. Using a future frees us from having to think about all of that.

So let’s change the owner definition to make it a future. Once it has been defined as a future, we now have dynamic access to the expression and can go about adding and deleting person records, while being able to query any building record to see who owns it.

DEFINE FIELD owners
	ON TABLE building
	VALUE <future> { (SELECT * FROM person WHERE $parent.id IN properties) };

With this definition in place, we can update the person and building records as much as we want and the owners field will update accordingly.

CREATE person:the_nobleman SET
    name = "The Nobleman",
    class = "Count",
    money = 50;

CREATE building:old_castle SET
  name = "Old castle",
  kind = "castle";

UPDATE person:the_nobleman SET properties = [building:old_castle];

// Will show one owner
SELECT * FROM building;

CREATE person:the_noblewoman SET
    name = "The Noblewoman",
    properties = [building:old_castle];

// Now two owners
SELECT * FROM building;

DELETE person;

// Now zero owners
SELECT * FROM building;

Here are the results from the three SELECT queries above, showing the changing state of the owners field inside the building:old_castle record.

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

[
	{
		id: building:old_castle,
		kind: 'castle',
		name: 'Old castle',
		owners: [
			{
				class: 'Count',
				id: person:the_nobleman,
				money: 50,
				name: 'The Nobleman',
				properties: [
					building:old_castle
				]
			}
		]
	}
]

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

[
	{
		id: building:old_castle,
		kind: 'castle',
		name: 'Old castle',
		owners: [
			{
				class: 'Count',
				id: person:the_nobleman,
				money: 50,
				name: 'The Nobleman',
				properties: [
					building:old_castle
				]
			},
			{
				id: person:the_noblewoman,
				name: 'The Noblewoman',
				properties: [
					building:old_castle
				]
			}
		]
	}
]

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

[
	{
		id: building:old_castle,
		kind: 'castle',
		name: 'Old castle',
		owners: []
	}
]

Multiple table types in relation queries and using the ? wildcard operator

We learned about bidirectional querying with the <-> operator in Chapter 8, which let us create marriages and friendships that could only be defined once thanks to a unique index on a key which was created by the sorted Record IDs found at the in and out fields.

Here is a similar example to the one we saw in that chapter.

DEFINE FIELD key
	ON TABLE friends_with
	VALUE <string>[in, out].sort();
DEFINE INDEX only_unique
	ON TABLE friends_with
	FIELDS key
	UNIQUE;

Thanks to this key, we can only RELATE with friends_with once, and the second RELATE statement will fail.

CREATE cat:one SET name = "Mr. Meow";
CREATE cat:two SET name = "Mrs. Meow";
RELATE cat:one->friends_with->cat:two;
RELATE cat:two->friends_with->cat:one;

Response
-------- Query --------

[
	{
		id: cat:one,
		name: 'Mr. Meow'
	}
]

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

[
	{
		id: cat:two,
		name: 'Mrs. Meow'
	}
]

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

[
	{
		id: friends_with:8ftj8k72f2rdbwwe56f2,
		in: cat:one,
		key: '[cat:one, cat:two]',
		out: cat:two
	}
]

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

"Database index `only_unique` already contains '[cat:one, cat:two]', with record `friends_with:8ftj8k72f2rdbwwe56f2`"

After that, a bit of query magic with the array::complement() function lets us find every friend that a cat has, except for itself.

DEFINE FIELD key
	ON TABLE friends_with
	VALUE <string>[in, out].sort();
DEFINE INDEX only_unique
	ON TABLE friends_with
	FIELDS key
	UNIQUE;

CREATE cat:one SET name = "Mr. Meow";
CREATE cat:two SET name = "Mrs. Meow";
RELATE cat:one->friends_with->cat:two;

SELECT
	name,
	array::complement(<->friends_with<->cat.*, [$this]) AS friends
FROM cat;

Response
[
	{
		friends: [
			{
				id: cat:two,
				name: 'Mrs. Meow'
			}
		],
		name: 'Mr. Meow'
	},
	{
		friends: [
			{
				id: cat:one,
				name: 'Mr. Meow'
			}
		],
		name: 'Mrs. Meow'
	}
]

Now what happens if a dog would like to be friends too? We can easily set up a the relation:

CREATE dog:one SET name = "Mr. Bark";
RELATE dog:one->friends_with->cat:one;
RELATE dog:one->friends_with->cat:two;

But the <->friends_with<->cat part of the SELECT query above is only looking for cats at the end of the friends_with graph table, instead of both cats and dogs. We can fix this by changing <->cat to <->(cat, dog), and by selecting FROM cat, dog instead of just FROM cat. This will now look for all possible cat or dog friends of each cat and dog in the database.

DEFINE FIELD key
	ON TABLE friends_with
	VALUE <string>[in, out].sort();
DEFINE INDEX only_unique
	ON TABLE friends_with
	FIELDS key
	UNIQUE;

CREATE cat:one SET name = "Mr. Meow";
CREATE cat:two SET name = "Mrs. Meow";
CREATE dog:one SET name = "Mr. Bark";

RELATE cat:one->friends_with->cat:two;
RELATE dog:one->friends_with->cat:one;
RELATE dog:one->friends_with->cat:two;

SELECT
	name,
	array::complement(<->friends_with<->(cat, dog).*, [$this]) AS friends
FROM cat, dog;

Response
[
	{
		friends: [
			{
				id: dog:one,
				name: 'Mr. Bark'
			},
			{
				id: cat:two,
				name: 'Mrs. Meow'
			}
		],
		name: 'Mr. Meow'
	},
	{
		friends: [
			{
				id: cat:one,
				name: 'Mr. Meow'
			},
			{
				id: dog:one,
				name: 'Mr. Bark'
			}
		],
		name: 'Mrs. Meow'
	},
	{
		friends: [
			{
				id: cat:one,
				name: 'Mr. Meow'
			},
			{
				id: cat:two,
				name: 'Mrs. Meow'
			}
		],
		name: 'Mr. Bark'
	}
]

If you would like to match on any record type, you can use the ? wild card operator instead. Using this, we could see each of a cat’s friends, be they cats, dogs, humans, or anything else.

SELECT
	name,
	array::complement(<->friends_with<->(?).*, [$this]) AS friends FROM cat;

Here is another simpler example of the ? operator in action. Landevin, who is in Europe, has recently seen an interesting comet and has also visited a city known as Lutetia. At the same time, a villager from Lutetia noticed him and wondered just what this suspicious outsider was up to.

We can represent these pieces of data and their relations with some pretty simple queries.

CREATE comet:unknown_comet;
CREATE city:lutetia            SET name = "Lutetia";
CREATE person:landevin         SET name = "Landevin";
CREATE person:lutetia_villager SET name = "Some villager";

RELATE person:landevin->saw->comet:unknown_comet;
RELATE person:landevin->visited->city:lutetia;
RELATE person:lutetia_villager->saw->person:landevin;

Landevin at this point has three relations in total: he visited somewhere, he saw something, and he was seen. If we want to see them all, we could use the ? operator to ask SurrealDB to return whatever it finds at the end of each graph table related to this person:landevin record.

SELECT
	*,
	->? AS did,
	<-? AS done_to,
	<->? AS everything
FROM person:landevin;

Response
[
	{
		did: [
			saw:prkolwy3aaak7vp5yrub,
			visited:vdt5e5jz1zk0gpeeoz5r
		],
		done_to: [
			saw:67xhm5pvmdc4h1sswa8p
		],
		everything: [
			saw:67xhm5pvmdc4h1sswa8p,
			saw:prkolwy3aaak7vp5yrub,
			visited:vdt5e5jz1zk0gpeeoz5r
		],
		id: person:landevin,
		name: 'Landevin'
	}
]

And for something a little more readable, we can also add .* to display all the fields of these related records. This next query will show all the fields of the records Landevin did something to, followed by all the fields of the records that did something to him.

SELECT
	*,
	->?.out.* AS did_something_to,
	<-?.in.* AS done_by
FROM person:landevin;

Response
[
	{
		did_something_to: [
			{
				id: comet:unknown_comet
			},
			{
				id: city:lutetia,
				name: 'Lutetia'
			}
		],
		done_by: [
			{
				id: person:lutetia_villager,
				name: 'Some villager'
			}
		],
		name: 'Landevin'
	}
]

A WHERE clause can also be added to a relation query. If we only wanted to see records that had a name, we could add a WHERE clause to filter out the records that don’t have this information (in this case, just the mysterious comet). That gives us the query below which shows everything that has a relation to Landevin, as long as it has a name.

SELECT
	*,
    ->(? WHERE $this.out.name IS NOT NONE).out.* AS did_something_to,
    <-(? WHERE $this.in.name  IS NOT NONE).in.*  AS done_to
FROM person:landevin;

[
	{
		did_something_to: [
			{
				id: city:lutetia,
				name: 'Lutetia'
			}
		],
		done_to: [
			{
				id: person:lutetia_villager,
				name: 'Some villager'
			}
		],
		id: person:landevin,
		name: 'Landevin'
	}
]

The random stuff

To finish up this chapter, we will quickly go over some random bits and pieces that are good to know and quick to pick up at this point now that we have become more advanced users of SurrealDB.

Recursive functions

One convenient aspect to defining a function is that it can call itself! Let’s see what happens if we create a function that simply calls itself over and over again.

DEFINE FUNCTION fn::recurse() {
    fn::recurse();
};

fn::recurse();

Instead of freezing up the database, SurrealDB recognizes that this would lead to too much “computation depth” and simply gives up. As the error message notes, infinite recursion can happen not only inside a function, but also due to subqueries and futures.

'Reached excessive computation depth due to functions, subqueries, or futures'

We can create a different function that only calls itself if a random bool is true, which will keep it from being called infinitely. Instead, you should see anywhere from 1 to some other small number of person records created.

DEFINE FUNCTION fn::create_recursive() {
    CREATE person;
    IF rand::bool() {
        fn::create_recursive();
    }
};

fn::create_recursive();
RETURN count(SELECT id FROM person);

For something more useful, here is a demonstration of the Fibonacci sequence as a SurrealQL function.

DEFINE FUNCTION fn::fibonacci($num: int) -> int {
    RETURN
             IF $num = 0 { 0 }
        ELSE IF $num = 1 { 1 }
        ELSE { fn::fibonacci($num - 1) + fn::fibonacci($num - 2) }
};

// Returns 377
RETURN fn::fibonacci(14);

The TEMPFILES and TIMEOUT keywords

These keywords were mentioned once: way, way back at the very beginning of the intro of this book. Time to give them some attention!

TEMPFILES is a recent addition to SurrealDB’s syntax, and is used for when you need to make queries that are so large that all the memory required ends up giving your computer a hard time. While memory is by default the most performant option, that is of no help when your computer has completely run out of spare memory and needs to start swapping back and forth with whatever spare resources it has.

In this case, you can add the TEMPFILES keyword to the end of a SELECT statement to instruct the computer to process the statement inside temporary files, rather than memory. While much slower than memory in most cases, this may result in a significant speedup for massive queries that would otherwise be a burden on your computer.

SELECT * FROM person TEMPFILES;

Similarly, you can also add TIMEOUT followed by a duration if you want SurrealDB to quit early if a query is taking too long. Note that the actual execution time will be somewhat longer than the timeout, which becomes more noticeable for queries that process massive amounts of data. The output from a query that creates 100000 records and another much larger one that creates half a million records shows the difference:

ns/db> CREATE |person:100000| RETURN NONE;
-- Query 1 (execution time: 2.3801824s)
[]

ns/db> CREATE |person:500000| RETURN NONE;
-- Query 1 (execution time: 13.3432103s)
[]

ns/db> CREATE |person:100000| RETURN NONE TIMEOUT 1s;
-- Query 1 (execution time: 1.0290052s)
'The query was not executed because it exceeded the timeout'

ns/db> CREATE |person:500000| RETURN NONE TIMEOUT 1s;
-- Query 1 (execution time: 1.1637722s)
'The query was not executed because it exceeded the timeout'

The EXPLAIN keyword

You can gain some insight into what method SurrealDB is using in your queries by adding the EXPLAIN keyword. Take the following two queries on some simple person records, one of which uses an index, and one that doesn’t.

DEFINE INDEX unique_name ON TABLE person FIELDS name UNIQUE;
CREATE person SET name = "Fernçois", address = "10 Boulevard de Lutetia" RETURN NONE;

SELECT * FROM person WHERE name = "Fernçois" EXPLAIN;
SELECT * FROM person WHERE address CONTAINS "Lutetia" EXPLAIN;

Response
-------- Query --------

[
	{
		detail: {
			plan: {
				index: 'unique_name',
				operator: '=',
				value: 'Fernçois'
			},
			table: 'person'
		},
		operation: 'Iterate Index'
	},
	{
		detail: {
			type: 'Memory'
		},
		operation: 'Collector'
	}
]

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

[
	{
		detail: {
			table: 'person'
		},
		operation: 'Iterate Table'
	},
	{
		detail: {
			type: 'Memory'
		},
		operation: 'Collector'
	}
]

Note that ‘Iterate Table’ (also known as a table scan) shows up once an index has not been found, which means a scan over every record in the database. As the fallback option, this tends to be less performant than other ways to query your data.

We can demonstrate this by first creating a bunch of person objects with an id going from 1 to 100000. We will then compare two queries, one of which uses the WHERE clause (which will perform a table scan), while the other uses the .. range syntax which will be much faster.

To do a quick comparison, we will create a variable to hold the time before the operation, and then will subtract it from the time after the operation to get the duration. And to ensure that the SELECT statement doesn’t clutter up the screen, we’ll assign it to a variable called $nothing that we will never read.

CREATE |person:1..100000| RETURN NONE;

LET $now = time::now();
LET $nothing = SELECT * FROM person WHERE id >= person:99900;
RETURN "Time elapsed: " + <string>(time::now() - $now);

LET $now = time::now();
LET $nothing = SELECT * FROM person:99900..;
RETURN "Time elapsed: " + <string>(time::now() - $now);
DELETE person;

Adding the EXPLAIN keyword to the end of these two queries gives the details.

-------- Query 1 (400.001µs) --------

[
	{
		detail: {
			table: 'person'
		},
		operation: 'Iterate Table'
	},
	{
		detail: {
			type: 'Memory'
		},
		operation: 'Collector'
	}
]

-------- Query 2 (99.999µs) --------

[
	{
		detail: {
			range: [
				NONE,
				NULL
			],
			table: 'person'
		},
		operation: 'Iterate Range'
	},
	{
		detail: {
			type: 'Memory'
		},
		operation: 'Collector'
	}
]

Object functions

The freeform nature of objects makes them both flexible and also somewhat unpredictable and tough to work with at times. SurrealDB has a number of functions that work on objects that can help make them easier to work with.

Three of them turn an object into an array (or array of arrays), made out of:

• its keys, using object::keys()
• its values, using object::values()
• both keys and values, using object::entries()

Let’s give two of these functions a try with some of the possible records for the Minitels in Europe.

INSERT INTO terminal [
	{
		id: 1,
		location: (41.8924, 12.9271),
		location_notes: 'On the corner of Piazza San Marino',
		metadata: {
			employee_notes: 'San Marino was known in the 21st century as Italy, though the city name Rome remains the same. Locals have been cooperative so far.',
			terminal_name: 'Central Rome, San Marino'
		},
		password: crypto::argon2::generate('BluebirdsFlyAtMidnight')
	},
	{
		id: 2,
		metadata: {
			terminal_name: 'Brescia, San Marino'
		},
		password: crypto::argon2::generate('TatersPotatoesBoilemMashem')
	},
	{
		id: 3,
		metadata: {
			employee_notes: 'Quite a nice place to set up a terminal. Best, -- Sammael'
		},
		password: crypto::argon2::generate('SaangrealPlease')
	}
];

SELECT $object.keys() AS keys,
	$object.values() AS values
FROM terminal;

Response
[
	{
		keys: [
			'id',
			'location',
			'location_notes',
			'metadata',
			'password'
		],
		values: [
			terminal:1,
			(41.8924, 12.9271),
			'On the corner of Piazza San Marino',
			{
				employee_notes: 'San Marino was known in the 21st century as Italy, though the city name Rome remains the same. Locals have been cooperative so far.',
				terminal_name: 'Central Rome, San Marino'
			},
			'$argon2id$v=19$m=19456,t=2,p=1$IgkmvUIF51Nd0n6Xn0bb+g$4guoUlwt6zqpLG9D5we2Q/2UEHQ8bm1rqXmR2MHqo+s'
		]
	},
	{
		keys: [
			'id',
			'metadata',
			'password'
		],
		values: [
			terminal:2,
			{
				terminal_name: 'Brescia, San Marino'
			},
			'$argon2id$v=19$m=19456,t=2,p=1$mALZuMrL55DICIn1gTRS5w$48YBNsv+riqNjL1mQIRHC2qJ1XYG6eniwJt3AuVUjAc'
		]
	},
	{
		keys: [
			'id',
			'metadata',
			'password'
		],
		values: [
			terminal:3,
			{
				employee_notes: 'Quite a nice place to set up a terminal. Best, -- Sammael'
			},
			'$argon2id$v=19$m=19456,t=2,p=1$Hnsm0kFXaG+ovwTtx0CZMA$0co//dI1ZYL4gBEqT3zbsUp6LBmbjMJET/LNflHmhJ0'
		]
	}
]

SurrealDB has two more object functions: object::len() which returns the number of key-value pairs, and object::from_entries() which lets you build an object from an array of keys and values.

RETURN object::from_entries([
   [ "id", 1 ],
   [ "location", (41.8924, 12.9271) ],
   [ "location_notes", 'On the corner of Piazza San Marino' ]
]);

Response
{
	id: 1,
	location: (41.8924, 12.9271),
	location_notes: 'On the corner of Piazza San Marino'
}

The next chapter will be sort of a continuation of this one, but dipping into even deeper and darker parts of SurrealDB - and even some of its source code!

CREATE cat:one SET name = "Kitten one";
CREATE cat:two SET name = "Cat two";
RELATE cat:one->likes->cat:two SET relationship = <future> { cat:one.name + " likes " + cat:two.name };
SELECT * FROM likes;
// Cat is all grown up now
UPDATE cat:one SET name = "Cat one";
SELECT * FROM likes;

INSERT INTO planet [
    {
        name: "Mercury"
    },
    {
        Name: "Venus"
    },
    {
        NAME: "Mars"
    },
    {
        name: "Jupiter"
    }
];

FOR $planet IN SELECT * FROM planet {
    LET $new_planet = {
        id: $planet.id,
        name: IF "name" IN $planet.keys() { $planet.name }
              ELSE IF "NAME" IN $planet.keys() { $planet.NAME }
              ELSE { $planet.Name }
    };
    CREATE new_planet CONTENT $new_planet;
};

SELECT * FROM new_planet;

Response
[
	{
		id: new_planet:2x0fcr87h5y6ferce9y2,
		name: 'Mars'
	},
	{
		id: new_planet:31ov6otoni6igvgcnomz,
		name: 'Venus'
	},
	{
		id: new_planet:mil4wx8f8a5bgx03fpvd,
		name: 'Mercury'
	},
	{
		id: new_planet:wc0atjga399hd5xd5aw9,
		name: 'Jupiter'
	}
]

CREATE stuff_with_array:one SET data = <array>1..20 RETURN NONE;

fn::pull_off(stuff_with_array:one);

SELECT * FROM stuff_with_array;

DEFINE FUNCTION OVERWRITE fn::pull_off($input: record<stuff_with_array>) {
    IF $input.data.len() = 1 {
        RETURN "Done!";
    } ELSE IF rand::bool() {
        UPDATE $input SET data = data.remove(0);
        fn::pull_off($input);
    } ELSE {
        UPDATE $input SET data = data.remove(-1);
        fn::pull_off($input);
    }
};

CREATE blog_article:comet SET content = "Did you know that comets spend most of their time...";
CREATE comment:one SET content = "I had no idea!";
RELATE comment:one->on->blog_article:comet;
CREATE review:one SET content = "Last week I found this interesting article on comets which...";
RELATE review:one->on->blog_article:comet;

SELECT <-on<-comment.* FROM blog_article:comet;

SELECT <-(on, about)<-(review, comment).* FROM blog_article:comet;

Response
[
	{
		"<-(on, about)": {
			"<-(comment, review)": [
				{
					content: 'I had no idea!',
					id: comment:one
				},
				{
					content: 'Last week I found this interesting article on comets which...',
					id: review:one
				}
			]
		}
	}
]

DEFINE FIELD interactions ON TABLE blog_article <future> { (SELECT <-(on, about)<-(comment, review).* FROM $this) };

DEFINE FIELD interactions ON TABLE blog_article <future> { (SELECT VALUE <-(on, about)<-(comment, review).* FROM $this) };

DEFINE FIELD interactions
	ON TABLE blog_article
	VALUE <future> { (SELECT VALUE <-(on, about)<-(comment, review).* FROM $this) };

CREATE blog_article:comet
	SET content = "Did you know that comets spend most of their time...";
CREATE comment:one
	SET content = "I had no idea!";
CREATE review:one
	SET content = "Last week I found this interesting article on comets which...";

RELATE comment:one->on->blog_article:comet;
RELATE review:one->about->blog_article:comet;

SELECT <-(on, about)<-(comment, review).* FROM blog_article:comet;

SELECT * FROM blog_article;

CREATE comment:two
	SET content = "Kind of confused";
RELATE comment:two->on->blog_article:comet;

SELECT * FROM blog_article;