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 shows average salary by department, role and level.

The first step is to compute all the averages, so that we can apply the pivot:

r.db('test')
 .table('salaries')
 .group('Department', 'role', 'level')
 .avg('salary')
 .ungroup()

In SQL, this would have been equivalent to:

SELECT Department, role, level, avg(salary)
GROUP BY 1, 2, 3

Notice how in SQL this would only return rows for values that exist. Values for each combination of role and level are in different rows, rather than columns.

To start the pivot, we first turn each result into an array:

 ...
 .map( (doc) =>
    [doc('group')(0), 
     doc('group')(1), 
     doc('group')(2), 
     doc('reduction')]
 )

This allows us to apply transformations to all the row values, so we can use some as column names.

Once we do this, we can combine the role and level fields into column names – i.e. to produce names like “Engineer_1”, “Engineer_2”, “Manager_1”, “Manager_2”, and so on.

...
  .map( (doc) =>
    r.object(
     'Department', 
     doc(0), 
     doc(1).add('_').add(doc(2).coerceTo('string')), 
     doc(3))
  )

The above expression is requires you to coerce numeric values to strings – “add”expects to operate on two values of the same type.

If you get null reference errors, you can start this with “expr(”).add(…)” to ensure the absence of null values.

...
 .group('Department')
 .reduce(
   (a, b) => a.merge(b)
 )

This produces a nice looking result. Note that we still have ‘reduction’ bit.

r.db('test')
 .table('salaries')
 .group('Department', 'role', 'level')
 .avg('salary')
 .ungroup()
 .map( (doc) => [
   doc('group')(0), 
   doc('group')(1), 
   doc('group')(2), 
   doc('reduction')
  ])
 .map( (doc) =>
   r.object(
     'Department', 
     doc(0), 
     doc(1).add('_').add(doc(2).coerceTo('string')), 
     doc(3))
 )
 .group('Department')
 .reduce(
   (a, b) => a.merge(b)
 )
 .ungroup()
 .map( 
   (doc) => doc('reduction') 
 )