RethinkDB: FULL OUTER JOIN example

If you want to do a full outer join in RethinkDB you can, although it will be noticeably slower than other queries. If this seems strange, this is a somewhat common use case in reporting or scraping, where you may have a subset of all available fields at any point. The way to execute this […]

RethinkDB: RIGHT JOIN Example

RethinkDB has a function called “outerJoin”, which actually does a left join. For example, this finds users and adds their audit trail history (if any): r.db(’test’) .table(’user_actions’) .outerJoin(r.table(’users’), (action, user) => action(’user_id’).eq(user(’id’))) .zip() If instead you want to start with audit history and add users (if they exist) you have to swap everything manually, since […]

RethinkDB: LEFT JOIN Example

In RethinkDB, a LEFT JOIN looks exactly like INNER JOIN: r.db(’test’) .table(’user_actions’) .outerJoin(r.table(’users’), (action, user) => action(’user_id’).eq(user(’id’))) .zip() A couple notes here: to achieve an expected result you need to add “.zip” at the end, otherwise you get a data structure with “left” and “right”. It’s also important to note that despite the name this […]

RethinkDB: INNER JOIN Example

One way to to do an inner join in RethinkDB with the eqJoin function: r.db(’test’) .table(’user_actions’) .filter({file_name: ‘test2.pdf’}) .eqJoin(’user_id’, r.table(’users’)) The biggest disadvantage to this API is that you can’t easily reverse the order of the tables. In this case, I’m taking users and adding audit information to them, and in SQL I would be […]

RethinkDB: coalesce example

In SQL, the coalesce or NVL function takes null values and replaces them with a defined value. To replicate this in RethinkDB, you can use the ‘branch’ function to test for null and remove it: r.db(’test’) .table(’users’) .map( (doc) => { return { first_name: null } } ).map( (doc) => { return { first_name: r.branch( […]

RethinkDB: CASE WHEN example

There are a couple ways to mimic the SQL “CASE WHEN” in RethinkDB. If the rows don’t have values in some columns, in SQL you’d default these with “CASE WHEN attr IS NULL … ELSE END”. To fix these, you can use map / merge: r.db(’test’).table(’users’) .map( (doc) => { return r.expr( { first_name: ”, […]

RethinkDB: Fixing error “e: No attribute `last_name` in object:”

If you run a ‘map’ in RethinkDB and an attribute is not found, you can get an error like so: r.db(’test’) .table(’users’) .map( (doc) => { return { first_name: doc(’first_name’), last_name: doc(’last_name’) } } ) e: No attribute `last_name` in object: There are a couple ways to fix this, depending on your needs. You can […]

RethinkDB: Upsert Example

To create a test case, we’ll make a table: r.db(’test’).table(’users’).insert([{ first_name: ‘gary’, last_name: ‘sieling’ }, { first_name: ‘melissa’ }, { last_name: ‘a test’ }]) 3 rows returned. Displaying rows 1-3 id first_name last_name 1 46454a3e-ba9e-4991-bc7f-53f1e5c19906 gary sieling 2 450e77d6-d5c6-4533-b2d7-c0158bb097c9 melissa undefined 3 e08b00c8-b8d2-465b-9886-ee014d14e751 undefined a test If you try to re-insert these, you’ll get a […]

RethinkDB: Pivot Table Example

Making a pivot table is a relatively common reporting task. I thought it’d be interesting to demonstrate how to do this with RethinkDB, as it shows how to build a complex query. In my sample data, we’ll use a table that lists salary information for a list of people. We’ll generate a pivot table that […]

RethinkDB: SELECT DISTINCT example

Using “distinct” in RethinkDB is as simple as adding an additional predicate: r.db(’test’) .table(’salaries’) .pluck(’role’) .distinct() 3 rows returned in 42ms. role 1 undefined 2 Designer 3 Manager You can add as many columns as you need: r.db(’test’) .table(’salaries’) .pluck(’role’, ‘level’) .distinct() 7 rows returned in 42ms. level role 1 undefined undefined 2 1 Designer […]