# SPLIT

The SPLIT clause in SurrealQL is used to split the results of a query based on a specific field, particularly when dealing with arrays.

The `SPLIT` clause in SurrealQL is used to split the results of a query based on a specific field, particularly when dealing with arrays. This is useful in scenarios where you want to treat each element of an array as a separate row in the result set. It can be particularly helpful in data analysis contexts where you need to work with individual elements of an array separately.

## Syntax

```syntax title="Clause Syntax"
SPLIT [ON] @field
```

Suppose you have a user table with a field emails that contains an array of email addresses for each user. You want to list each email address as a separate record.

Here's how you can use the SPLIT clause in SurrealQL:

```surql
CREATE user SET
    name = "John Doe",
    emails = ["john@example.com", "doe@example.com"];

-- Split the results by each value in the emails array
SELECT * FROM user SPLIT emails;
```

Explanation:
- `CREATE user SET ...`: This creates a user record with a name and an array of email addresses.
- `SELECT * FROM user SPLIT emails`: This query selects all fields from the user table and splits the results based on the emails field. Each email address in the `emails` array will now be in a field of the same name that only contains a single value.

Output:
The output of the query will be:

```surql
[
	{
		emails: 'john@example.com',
		id: user:unjgil312jvvxfbdj706,
		name: 'John Doe'
	},
	{
		emails: 'doe@example.com',
		id: user:unjgil312jvvxfbdj706,
		name: 'John Doe'
	}
]
```

## Using `SPLIT` to restructure collected paths

One practical use case with `SPLIT` is returning every possible combination of the relations inside multiple graph paths. For instance, take the following data below that represents the relations between Canada the country, its provinces, and their cities.

```surql
CREATE country:canada;
CREATE province:bc, province:alberta;
CREATE city:vancouver, city:victoria, city:edmonton, city:calgary;

RELATE [city:vancouver, city:victoria]->in->province:bc;
RELATE [city:edmonton, city:calgary]->in->province:alberta;
RELATE [province:bc, province:alberta]->in->country:canada;
```

A graph query on both of these paths shows all of the provinces and cities.

```surql
SELECT 
    id AS country,
    <-in<-province AS provinces,
    <-in<-province<-in<-city AS cities FROM ONLY country:canada;
```

```surql title="Output"
{
	cities: [
		city:calgary,
		city:edmonton,
		city:vancouver,
		city:victoria
	],
	country: country:canada,
	provinces: [
		province:alberta,
		province:bc
	]
}
```

Using `SPLIT` in this case transforms the output from a collection of paths centred on the `country:canada` record into an array of objects, each representing every possible combination of cities and provinces inside the country.

```surql
SELECT
    id AS country,
    <-in<-province AS province,
    <-in<-province<-in<-city AS city FROM country:canada
    SPLIT city, province;
```

```surql title="Output"
[
	{
		city: city:calgary,
		country: country:canada,
		province: province:alberta
	},
	{
		city: city:calgary,
		country: country:canada,
		province: province:bc
	},
	{
		city: city:edmonton,
		country: country:canada,
		province: province:alberta
	},
	{
		city: city:edmonton,
		country: country:canada,
		province: province:bc
	},
	{
		city: city:vancouver,
		country: country:canada,
		province: province:alberta
	},
	{
		city: city:vancouver,
		country: country:canada,
		province: province:bc
	},
	{
		city: city:victoria,
		country: country:canada,
		province: province:alberta
	},
	{
		city: city:victoria,
		country: country:canada,
		province: province:bc
	}
]
```

An example of the same query then mapped into a set of unique keys for serialisation:

```surql
(SELECT
    id,
    <-in<-province AS province,
    <-in<-province<-in<-city AS city FROM country:canada
    SPLIT city, province)
.map(|$obj| 
    <string>$obj.id.id() 
    + '|' 
    + <string>$obj.province.id() 
    + '|' 
    + <string>$obj.city.id()
);
```

```surql title="Output"
[
	'canada|alberta|calgary',
	'canada|bc|calgary',
	'canada|alberta|edmonton',
	'canada|bc|edmonton',
	'canada|alberta|vancouver',
	'canada|bc|vancouver',
	'canada|alberta|victoria',
	'canada|bc|victoria'
]
```