Reporting on joined data

[Matthew Buckett]

I'm looking at trying to report on some data held in CouchDB, I have 2
types of object stored, users and activities. An example user id:

{
  "_id": "37453929...",
  userId: "1234",
  type: "user",
  name: "John Smith",
  department: "english"
}

There will be multiple users and there is a small set of possible values
for the department. An example activity is:

{
  "_id": "736489...",
  userId: "1234",
  type: "activity",
  title: "Doing some work.",
  hours: 2
}
each user will have multiple activities.

I would like a view showing the total number of hours per department, but I
can't see how to easily do it. I was initially thinking I could perform a
map-reduce to build map the documents by userId, and then use a reduce to
build a super document that contains the department and the hours together.
So I ended up with something like:

{
  user: {.... department: "english" },
  activites: [
    {.. hours: 2 }, ...
  ]
}

However this resulting document wouldn't be keyed on the department and I
can't perform a further map-reduce to switch it to be keyed off the
department.

Thanks in advance.