CouchDB MapReduce query for relational data

Question

Before I start, please don't tell me to use SQL. This is a small problem in a bigger context and I can't and don't want to use a relational database here. I know the problem is pretty easy to solve in SQL.

There are three types of documents:

Players
Teams
Sponsors

Teams belong to one sponsor but have many players. A player can be in multiple teams and a sponsor can have multiple teams.

Players 1 --- N Teams N --- 1 Sponsors

I put the player document ids into the Teams document in an array:

players: ["payer1","player2",...]

Now I want all (distinct, only named once) sponsors for a specific player:

Player1: Sponsor1, Sponsor2, Sponsor3,...

It's a bit like the n-n example in the CouchDB Wiki, but since there are multiple keys in the team-players, that doesn't really work.

I created a gist with example data..

Any idea how to write the MapReduce function to get to this result?

The closest I got, but shows sponsors multiple times, with group level 1:

function(doc) {
  if(doc.type == "team")
    emit(doc.players,doc.sponsor);
}
function(keys, values) {
  return (values);
}

could be useful ? http://stackoverflow.com/questions/5511810/couchdb-view-equivalent-of-sum-group-by — ajreal, Nov 23 '11 at 16:45

score 4 · Accepted Answer · answered Nov 23 '11 at 17:03

You are close with your view, here it is modified slightly:

"playerSponsers": {
       "map": "function(doc) {
                if(doc.type == "team" && doc.players) {
                   for (i in doc.players) {
                      emit([doc.players[i], doc.sponsor],1);
                   }
                }
               }",
       "reduce": "_sum"
   }

And here is the query:

http://localhost:5984/test/_design/sports/_view/playerSponsors?group=true&group_level=2

You will get results like this:

{"rows":[
{"key":["Player1","Sponsor1"],"value":1},
{"key":["Player1","Sponsor2"],"value":1},
{"key":["Player1","Sponsor3"],"value":1},
{"key":["Player2","Sponsor1"],"value":1},
{"key":["Player2","Sponsor2"],"value":2},
{"key":["Player2","Sponsor3"],"value":1}
]}

If you want to get the sponsors for just one player, you can query like this

http://localhost:81/test/_design/sports/_view/playerSponsors?group=true&group_level=2&startkey=[%22Player1%22]&endkey=[%22Player1%22,{}]

CouchDB MapReduce query for relational data

1 Answers1