0

The SurrealDB documentation states that the concept of JOINs is replaced through the use of Record links. But is it still possible to JOIN tables based on arbitrary columns?

Consider the following tables:

CREATE user:1 SET
    name = 'User1',
    favouriteDay = 'Monday'
;
CREATE user:2 SET
    name = 'User2',
    favouriteDay = 'Tuesday'
;

CREATE motto:1 SET 
    day = 'Monday',
    motto = 'Best day of the week'
;
CREATE motto:2 SET
    day = 'Tuesday',
    motto = 'Second best day of the week'
;

Is it possible to write a query to get the following result (without changing the underlying data model)?

"result": [
  {
    "favouriteDay": "Monday",
    "id": "user:1",
    "name": "User1",
    "motto": "Best day of the week"
  },
  {
    "favouriteDay": "Tuesday",
    "id": "user:2",
    "name": "User2",
    "motto": "Second best day of the week"
  }
]
Tobias S.
  • 21,159
  • 4
  • 27
  • 45

1 Answers1

0

There is indeed a way to JOIN data in SurrealDB using SPLIT.

A query joining the two tables user and motto would look like this:

SurrealDB

SELECT id, name, favouriteDay, motto.motto as motto 
FROM (
    SELECT *, (SELECT * FROM motto) as motto 
    FROM user 
    SPLIT motto 
) 
WHERE favouriteDay = motto.day;

SQL-equivalent

SELECT id, name, favouriteDay, motto
FROM user JOIN motto ON user.favouriteDay = motto.day

Explanation:

We first query all the users and add every motto to each user.

SELECT *, (SELECT * FROM motto) as motto FROM user

We can then use SPLIT to unwind every element in the motto array of each user to a separate row.

SELECT *, (SELECT * FROM motto) as motto FROM user SPLIT motto

Lastly, we take the result of this query and filter all the rows where favouriteDay and motto.motto match. We also pull out the motto string from the motto object to format the output to the the desired result.

SELECT id, name, favouriteDay, motto.motto as motto FROM (
    SELECT *, (SELECT * FROM motto) as motto FROM user SPLIT motto 
) 
WHERE favouriteDay = motto.day;

[
   {
      "favouriteDay":"Monday",
      "id":"user:1",
      "motto":"Best day of the week",
      "name":"User1"
   },
   {
      "favouriteDay":"Tuesday",
      "id":"user:2",
      "motto":"Second best day of the week",
      "name":"User2"
   }
]
Tobias S.
  • 21,159
  • 4
  • 27
  • 45
  • You can simplify this a bit like this and get rid of the second `SELECT` statement: `SELECT id, name, favouriteDay, motto.motto as motto FROM (SELECT *, (SELECT * FROM motto) as motto FROM user SPLIT motto) WHERE favouriteDay == motto.day` – Gustavo Bezerra Sep 30 '22 at 23:02
  • @GustavoBezerra - Ah, thanks for noticing. That makes it a bit less verbose ;) although I am still worried about the efficiency of the query. Seems like this may not be viable for large tables. Hopefully get implement real JOINs in the future... – Tobias S. Sep 30 '22 at 23:27