CODEWITHSUNDEEP
couchdbpouchdb
Chris
Chris

Reputation: 4728

How to GROUP BY in a CouchDB reduce function

I am trying to get to grips with map/reduce queries when using PouchDB/CouchDB.

I have a lot of documents in my database but I need to create a design that queries the documents and gives me all of the unique team names as a key and then tells me

a) how many unique wards are within each team

b) the total number of jobs per team (across all wards)

The structure of my data is:

{
    "_id": "0448071807c0f37f53e06aab54034a42",
    "_rev": "6-13fd78ada9c8833ec36a01af0acd5957",
    "team": "Team A",
    "ward": "Ward A",
    "date": "2017-03-30",
    "person": "Alice",
    "bed": "Bed 001",
    "jobs": [1,2,3,4]
}

{
    "_id": "0448071807c0f37f53e06aab54034a42",
    "_rev": "6-13fd78ada9c8833ec36a01af0acd5957",
    "team": "Team A",
    "ward": "Ward B",
    "date": "2017-03-30",
    "person": "Bob",
    "bed": "Bed 001",
    "jobs": [1,2]
}   

{
    "_id": "0448071807c0f37f53e06aab54034a42",
    "_rev": "6-13fd78ada9c8833ec36a01af0acd5957",
    "team": "Team A",
    "ward": "Ward C",
    "date": "2017-03-30",
    "person": "Charles",
    "bed": "Bed 001",
    "jobs": [9,5]
}   

{
    "_id": "0448071807c0f37f53e06aab54034a42",
    "_rev": "6-13fd78ada9c8833ec36a01af0acd5957",
    "team": "Team B",
    "ward": "Ward 00",
    "date": "2017-03-30",
    "person": "David",
    "bed": "Bed 001",
    "jobs": [1]
}

The output I would expect would be like this:

Team A - 3 unique wards - 8 jobs

Team B - 1 unique ward - 1 job

e.g.

{
    "key": "Team A",
    "value": {
        "wards": 3,
        "jobs": 8
    }
}

{
    "key": "Team B",
    "value": {
        "wards": 1,
        "jobs": 1
    }
}

My map is currently:

{
   "all": {
       "map": "function(doc) { emit(doc.team, doc) }"
   }
}

It is the reduce where my struggle comes in.

EDIT

I have taken the suggestions used on CouchDB View equivalent of SUM & GROUP BY but this only goes half way towards my challenge.

If I use:

{
   "all": {
       "map": "function(doc) { emit([doc.team, doc.ward], 1) }",
       "reduce": "function(keys, values) { return sum(values); }"
   }
}

And then go to http://my-ip:5984/wardround_jobs/_design/teams/_view/all?group_level=1 then I see the unique teams (good) and the number of occurrences (also great) but I am unsure how I extend the reduce function to include the total number of jobs.

Upvotes: 1

Views: 3582

Answers (1)

Alexis Côté
Alexis Côté

Reputation: 3690

First, you have to emit the jobs length (has the number of jobs) :

function (doc) {
  emit([doc.team,doc.ward],doc.jobs.length);
}

Then, you need a reduce function like this :

function (keys, values, rereduce) {
  var stats = {uniq:0,jobs:0};
  if (rereduce) {
    for(var i=0;i<values.length;i++){
     stats.uniq += values[i].uniq;
     stats.jobs += values[i].jobs;
    }
    return stats;
  }
  stats.uniq = values.length;
  stats.jobs = sum(values);
  return stats;
}

For the first iteration, we return an object (stats) with the number of wards perm team (uniq) and the number of jobs (we sum the jobs length of every team/ward.

Then, for the rereduce, we simply aggregate the object`s values.

Upvotes: 2

Related Questions