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 inclined to reverse the order of the join. While this is functionally identical, you may find cases where this forces you to think differently about the process.

{
  "left": {
    "action":  "open file" ,
    "file_name":  "test2.pdf" ,
    "id":  "07123881-5d95-4596-b0b4-3b1f50860bfe" ,
    "user_id":  "450e77d6-d5c6-4533-b2d7-c0158bb097c9"
  } ,
  "right": {
    "first_name":  "test" ,
    "id":  "450e77d6-d5c6-4533-b2d7-c0158bb097c9" ,
    "work_history": [
      {
        "role":  "role 1"
      },
      {
        "role":  "role 2"
      }
    ]
  }
}

Note that the above result makes objects called “left” and “right” in the result, which is not typically what you’d want – you can fix this by adding .zip:

r.db('test')
 .table('user_actions')
 .filter({file_name: 'test2.pdf'})
 .eqJoin('user_id', r.table('users'))
 .zip()

The other way to do this is with “innerJoin” which requires more effort, but is more flexible and lets you write things that are exact equivalences to SQL:

r.db('test')
 .table('user_actions')
 .filter({file_name: 'test2.pdf'})
 .innerJoin(r.table('users'),
    (action, user) => 
      action('user_id').eq(user('id')))

And this is the final output:

{
  "action":  "open file" ,
  "file_name":  "test2.pdf" ,
  "first_name":  "test" ,
  "id":  "450e77d6-d5c6-4533-b2d7-c0158bb097c9" ,
  "user_id":  "450e77d6-d5c6-4533-b2d7-c0158bb097c9" ,
  "work_history": [
    {
      "role":  "role 1"
    } ,
    {
      "role":  "role 2"
    }
  ]
}