SQL to ReQL cheat sheet
SQL to ReQL cheat sheet
Terminology
SQL and RethinkDB share very similar terminology. Below is a table of terms and concepts in the two systems.
SQL | RethinkDB |
---|---|
database | database |
table | table |
row | document |
column | field |
table joins | table joins |
primary key | primary key (by default id ) |
index | index |
INSERT
This is a list of queries for inserting data into a database.
SQL | ReQL |
---|---|
INSERT INTO users(user_id, age, name) VALUES ("f62255a8259f", 30, Peter) | r.table("users").insert({ :user_id => "f62255a8259f", :age => 30, :name => "Peter" }) |
SELECT
This is a list of queries for selecting data out of a database.
SQL | ReQL |
---|---|
SELECT * FROM users |
r.table("users") |
SELECT user_id, name FROM users |
r.table("users") .pluck("user_id", "name") |
SELECT * FROM users WHERE name = "Peter" |
r.table("users").filter({ :name => "Peter" }) If you have a secondary index built on the field r.table("users") .get_all("Peter", :index => "name") |
SELECT * FROM users WHERE name = "Peter" AND age = 30 |
r.table("users").filter({ :name => "Peter", :age => 30 }) |
SELECT * FROM users WHERE name LIKE "P%" |
r.table("users").filter{ |row| row['name'].match("^P") } |
SELECT * FROM users ORDER BY name ASC |
r.table("users").order_by("name") |
SELECT * FROM users ORDER BY name DESC |
r.table("users").order_by( r.desc("name") ) |
SELECT user_id FROM users WHERE name = "Peter" ORDER BY name DESC |
r.table("users").filter({ :name => "Peter" }).order_by( r.desc("name") ).pluck("user_id") |
SELECT * FROM users LIMIT 5 SKIP 10 |
r.table("users").skip(10).limit(5) |
SELECT * FROM users WHERE name IN ('Peter', 'John') |
r.table("users").filter{ |doc| r.expr(["Peter", "John"]) .contains(doc["name"]) } If you have a secondary index built on the field r.table("users") .get_all("Peter", "John", :index => "name") |
SELECT * FROM users WHERE name NOT IN ('Peter', 'John') |
r.table("users").filter{ |doc| r.expr(["Peter", "John"]) .contains(doc["name"]) .not() } |
SELECT COUNT(*) FROM users |
r.table("users").count() |
SELECT COUNT(name) FROM users WHERE age > 18 |
r.table("users").filter{ |doc| (doc.has_fields("name") & doc["age"] > 18) }.count() |
SELECT AVG("age") FROM users |
r.table("users") .avg("age") |
SELECT MAX("age") FROM users |
r.table("users")["age"] .max() |
SELECT DISTINCT(name) FROM users |
r.table("users").pluck("name").distinct() |
SELECT * FROM users WHERE age BETWEEN 18 AND 65; |
r.table("users").filter{ |doc| (doc["age"] >= 18) & (doc["age"] <= 65) }If you have a secondary index built on the field age , you can run a more efficient query: r.table("users") .between(18, 65, :index => "age") |
SELECT name, 'is_adult' = CASE WHEN age>18 THEN 'yes' ELSE 'no' END FROM users |
r.table("users").map{ |user| { :name => user["name"], :is_adult => r.branch( user["age"] > 18 "yes", "no" ) } } |
SELECT * FROM posts WHERE EXISTS (SELECT * FROM users WHERE posts.author_id = users.id) |
r.table("posts") .filter{ |post| r.table("users") .filter{ |user| user.id == post.author_id }.count() > 0 } |
UPDATE
This is a list of commands for updating data in the database.
SQL | ReQL |
---|---|
UPDATE users SET age = 18 WHERE age < 18 |
r.table("users").filter{ |doc| doc["age"] < 18 }.update({ :age => 18 }) |
UPDATE users SET age = age+1 |
r.table("users").update{ |doc| { :age => doc["age"]+1 } } |
DELETE
This is a list of queries for deleting data from the database.
SQL | ReQL |
---|---|
DELETE FROM users |
r.table("users").delete() |
DELETE FROM users WHERE age < 18 |
r.table("users").filter{ |doc| doc["age"] < 18 }.delete() |
JOINS
This is a list of queries for performing joins between multiple tables.
SQL | ReQL |
---|---|
SELECT * FROM posts JOIN users ON posts.user_id = users.id |
r.table("posts").inner_join( r.table("users") ) { |post, user| post["user_id"] == user["id"] }.zip() Note: If you have an index (primary key or secondary index) built on the field of the right table, you can perform a more efficient join with eq_join. r.table("posts").eq_join("id", r.table("users"), :index => "id" ).zip() |
SELECT posts.id AS post_id, user.name, users.id AS user_id FROM posts JOIN users ON posts.user_id = users.id SELECT posts.id AS post_id, user.name, users.id AS user_id FROM posts INNER JOIN users ON posts.user_id = users.id |
r.table("posts").inner_join( r.table("users") ) { |post, user| post["user_id"] == user["id"] }.map { |post, user| :post_id => post["id"], :user_id => user["id"], :name => user["name"] } |
SELECT * FROM posts RIGHT JOIN users ON posts.user_id = users.id SELECT * FROM posts RIGHT OUTER JOIN users ON posts.user_id = users.id |
r.table("posts").outer_join( r.table("users") ) { |post, user| post["user_id"] == user["id"] }.zip() Note: You can perform more efficient r.table("posts").concat_map{ |post| r.table("users").get_all( post["id"], :index => "id" ).do{ |results| r.branch( results.count() == 0, [{:left => post}], results.map { |user| {:left => post, :right => user} } )} }.zip() |
SELECT * FROM posts LEFT JOIN users ON posts.user_id = users.id SELECT * FROM posts LEFT OUTER JOIN users ON posts.user_id = users.id |
r.table("posts").outer_join( r.table("users") ) { |user, post| post["user_id"] == user["id"] }.zip() r.table("posts").concat_map{ |post| r.table("users").get_all( post["id"], :index => "id" ).do{ |results| r.branch( results.count() == 0, [{:left => user}], results.map { |post| {:left => user, :right => post} } )} }.zip() |
AGGREGATIONS
This is a list of queries for performing data aggregation.
SQL | ReQL |
---|---|
SELECT category FROM posts GROUP BY category |
r.table("posts").map{ |doc| doc["category"] }.distinct() |
SELECT category, SUM('num_comments') FROM posts GROUP BY category |
r.table('posts') .group('category') .sum('num_comments') |
SELECT category, status, SUM('num_comments') FROM posts GROUP BY category, status |
r.table("posts") .group('category', 'status') .sum('num_comments') |
SELECT category, SUM(num_comments) FROM posts WHERE num_comments > 7 GROUP BY category |
r.table("posts").filter{ |doc| doc['num_comments'] > 7 }.group('category') .sum('num_comments') |
SELECT category, SUM(num_comments) FROM posts GROUP BY category HAVING num_comments > 7 |
r.table("posts") .group('category') .sum('num_comments') .ungroup() .filter{ |doc| doc["reduction"] > 7 } |
SELECT title, COUNT(title) FROM movies GROUP BY title HAVING COUNT(title) > 1 |
r.table("movies") .group("title") .count() .ungroup() .filter{ |doc| doc["reduction"] > 1 } |
TABLE and DATABASE manipulation
This is a list of queries for creating and dropping tables and databases.
SQL | ReQL |
---|---|
CREATE DATABASE my_database; |
r.db_create('my_database') |
DROP DATABASE my_database; |
r.db_drop('my_database') |
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(50), age INT); |
r.table_create('users', :primary_key => "id") Note: RethinkDB is a NoSQL database and does not enforce schemas. Note: The default primary key is |
TRUNCATE TABLE users; |
r.table("users").delete() |
DROP TABLE users; |
r.table_drop("users") |
Read More
Browse the following resources to learn more about ReQL:
© RethinkDB contributors
Licensed under the Creative Commons Attribution-ShareAlike 3.0 Unported License.
https://rethinkdb.com/docs/sql-to-reql/ruby/