CODEWITHSUNDEEP
couchdb
Bernd
Bernd

Reputation: 11

couchdb - Map Reduce - How to Join different documents and group results within a Reduce Function

I am struggling to implement a map / reduce function that joins two documents and sums the result with reduce.

First document type is Categories. Each category has an ID and within the attributes I stored a detail category, a main category and a division ("Bereich").

{
   "_id": "a124",
   "_rev": "8-089da95f148b446bd3b33a3182de709f",
   "detCat": "Life_Ausgehen",
   "mainCat": "COL_LEBEN",
   "mainBereich": "COL",
   "type": "Cash",
   "dtCAT": true
}

The second document type is a transaction. The attributes show all the details for each transaction, including the field "newCat" which is a reference to the category ID.

{
   "_id": "7568a6de86e5e7c6de0535d025069084",
   "_rev": "2-501cd4eaf5f4dc56e906ea9f7ac05865",
   "Value": 133.23,
   "Sender": "Comtech",
   "Booking Date": "11.02.2013",
   "Detail": "Oki Drucker",
   "newCat": "a124",
   "dtTRA": true
}

Now if I want to develop a map/reduce to get the result in the form:

e.g.: "Name of Main Category", "Sum of all values in transactions".

I figured out that I could reference to another document with "_ID:" and ?include_docs=true, but in that case I can not use a reduce function.

I looked in other postings here, but couldn't find a suitable example.

Would be great if somebody has an idea how to solve this issue.

Upvotes: 1

Views: 660

Answers (1)

Marcin Skórzewski
Marcin Skórzewski

Reputation: 2854

I understand, that multiple Category documents may have the same mainCat value. The technique called view collation is suitable to some cases where single join would be used in relational model. In your case it will not help: although you use two document schemes, you really have three level structure: main-category <- category <- transaction. I think you should consider changing the DB design a bit.

Duplicating the data, by storing mainCat value also in the transaction document, would help. I suggest to use meaningful ID for the transaction instead of generated one. You can consider for example "COL_LEBEN-7568a6de86e5e" (concatenated mainCat with some random value, where - delimiter is never present in the mainCat). Then, with simple parser in map function, you emit ["COL_LEBEN", "7568a6de86e5e"] for transactions, ["COL_LEBEN"] for categories, and reduce to get the sum.

Upvotes: 2

Related Questions