RethinkDB: GROUP BY example

If you add a ‘group’ to a RethinkDB query, it will behave similarly to a similar query in SQL, except that you need to ‘ungroup’ it afterward:

r.db('test')
 .table('salaries')
 .group('role')
 .sum('salary')
 .ungroup()
 .map({Title: r.row('group'), Amount: r.row('reduction')})
Amount
Title
1
403000
null
2
217000
Designer
3
425000
Manager

If, however, you group by two items, the grouping will come out as an array, rather than individual, named columns:

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

7 rows returned in 61ms.

group
reduction
1
[

  • null ,
  • null

]

403000
2
[

  • “Designer” ,
  • “1”

]

61000
3
[

  • “Designer” ,
  • “2”

]

71000
4
[

  • “Designer” ,
  • “3”

]

85000
5
[

  • “Manager” ,
  • “13”

]

81000
6
[

  • “Manager” ,
  • “2”

]

97000
7
[

  • “Manager” ,
  • “3”

]

247000

If this bothers you, and you want to make the result look like the output of SQL, you can add a mapping function to turn it back into rows, like so:

 .table('salaries')
 .group('role', 'level')
 .sum('salary')
 .ungroup()
 .map( 
   (x) => {
     return {
       Title: x('group')(0),
       Level: x('group')(1),
       Value: x('reduction')
     }
   } 
 )

7 rows returned in 54ms.

Level
Title
Value
1
null
null
403000
2
1
Designer
61000
3
2
Designer
71000
4
3
Designer
85000
5
13
Manager
81000
6
2
Manager
97000
7
3
Manager
247000

Leave a Reply

Your email address will not be published. Required fields are marked *