Monday, May 21, 2018

MongoDB/CouchDB: Join a table to itself

I have a relational data model I'm thinking about migrating to either MongoDB or CouchDB, and I'm trying to understand how the queries would work. Suppose I have two entities, Employees and Projects, and a many-to-many join table called Assignments. I want to query for all the projects on which two users collaborated. In SQL I could do something like this:




SELECT DISTINCT a1.project_id
FROM assignments a1, assignments a2
WHERE a1.project_id = a2.project_id
AND a1.employee_id = ?
AND a2.employee_id = ?


How would I do this in NoSQL, assuming I have Employee, Project, and Assignment "documents"? Or would you structure the documents differently, and how would that affect the query?



I'd be happy to hear answers both for Mongo's query API and Couch's map/reduce approach.

No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...