Joining documents in Couchbase and using aggregate functions

Question

I have these 4 documents

Document 1

{
  "First_Name": "dhw",
  "Type": "GLAccount",
  "Acc_no": 1,
  "Last_Name": "irtahow"
}

Document 2

{
  "Marks": 13,
  "Type": "GLEntry",
  "Acc_no": 1
}   

Document 3

{
  "Marks": 85,
  "Type": "GLEntry",
  "Acc_no": 1
}

Document 4

{
  "Marks": 93,
  "Type": "GLEntry",
  "Acc_no": 1
}

Now I want to use a sum function Documents 2,3 and 4 and then join it with Document 1. So the result of my query should be like

Acc_no First_Name   Last_Name      Sum(marks)

1       "dhw"       "irtahow"            191

So here you see I want to first sum all the marks of a particular Acc_No and then output its sum of marks and first name and last name. I am able to sum the marks but not able to simulate the join.

EDIT 1: This is what I have done so far

map function

function(doc, meta)
{

    emit(doc.No,doc.marks);
}

reduce function

_sum

I get sum of all marks of a particular Acc No. but I am not able to join it with the document containing first name and last name

Answer 1

I'm a little hesitant to suggest this, because I'm not sure what the impact on performance of this might be, but this will get you the data in a single query.

Map Function:

function (doc) {
  var type = doc.Type == "GLAccount" ? 0 : 1;
  var marks =  doc.Marks ? doc.Marks: 0;
  var key = [doc.Acc_no, type];
  if (type === 0){
    key.push(doc.First_Name, doc.Last_Name);
  }
  emit(key, marks);
}

Reduce Function:

_sum

URL Query:

http://host:5984/db/_design/design/_view/view?group=true

Result:

{"rows":[
{"key":[1,0,"dhw","irtahow"],"value":0},
{"key":[1,1],"value":191}
]}

You would then need to extract the data from the JSON.