CODEWITHSUNDEEP
rethinkdb
Simon
Simon

Reputation: 25

Summarise nested fields

I'm having a hard time getting a specific query right.

I have a schema following this style:

{
  "User":  "user1" ,
  "active": true ,
  "points": {
  "2015-07": 2 ,
  "2015-08": 5 ,
  "2015-09": 7 ,
  "2015-10": 1 ,
  "2015-11": 28 ,
  "2015-12": 5 ,
  "2016-01": 3
  }
} 

{
  "User":  "user2" ,
  "active": true ,
  "points": {
  "2015-01": 8 ,
  "2015-02": 4 ,
  "2015-09": 6 ,
  "2015-10": 12 ,
  "2015-11": 34 ,
  "2015-12": 1 ,
  "2016-01": 2
  }
}

How can I write a query that will return each user and the total amount of points?

Upvotes: 0

Views: 135

Answers (3)

Jippe
Jippe

Reputation: 714

For those of us unfortunate enough to still be stuck with a version of RethinkDB without a values function, the trick is to first retrieve all keys and use map to retrieve a sequence of values:

r.db("test").table("test").map(function (x) {  
  return x.merge({
    points: x("points").keys().map(function(key) {
      return x("points")(key)
    }).sum()
  }) 
})

Upvotes: 1

dalanmiller
dalanmiller

Reputation: 3662

Just a small typo in @Tryneus' example, .sum(...) should go at the end. Try this one:

r.db('test').table('test').map(function (x) {  
  return x.merge( {points: x('points').values().sum()}) 
})

Upvotes: 2

Tryneus
Tryneus

Reputation: 371

It should be simple enough to do this with a map, summing the values of the "points" object in each row. This code is in Javascript, so it should work in the Data Explorer, where r.expr(data) is the sequence of values you want to operate on.

r.expr(data).map(function (x) {
    return x.merge({points:r.sum(x('points').values())});
})

Upvotes: 0

Related Questions