MongoDB/CouchDB: Join a table to itself

Question

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.

score 3 · Answer 1 · answered May 29 '11 at 23:25

3

I can only share the view from the Couch, where I, um, relax every once in a while. First, you almost always have to intentionally forget SQL when dealing with document-based systems.

Second, you will have the option of either changing the structure and reducing the amount of entities to the minimum that won't require joins, or you could use views that will combine documents of different types into a single result set.

The former (redesign) is the preferred method, as joins are essentially a foreign approach for nosql, since normalization is not a requirement there. Document-based is not relational.

answered May 29 '11 at 23:25

Dennis Kreminsky

2,117
15
23

Okay, thanks for your response. But I'm having trouble figuring out a document model that will let me query for this kind of structure. Any ideas? – Paul A Jungwirth May 30 '11 at 01:52
I believe you'd have to choose the primary entity, I cannot help with that because I don't know anything about your application. If you center your system around users and project are relatively simple, wrap projects in users, or vice versa. If users and projects are equally important, include part of user's data in project/assignment document, so that it is sufficient for displaying whatever your join example fetched (with the drawback of needing to update two documents if one changes). Lastly, you can include user ID's in projects, and that would be a join. – Dennis Kreminsky May 30 '11 at 06:22
PS assignments will most likely have to merge into either users or projects, depending on your application. – Dennis Kreminsky May 30 '11 at 06:26

score 3 · Answer 2 · answered May 30 '11 at 09:54

In MongoDB you can do it like this. I use the interactive JavaScript Shell.

First create some users:

> db.so.employee.insert({name: "Joe"})
> db.so.employee.insert({name: "Moe"})
> db.so.employee.insert({name: "Bart"})
> db.so.employee.insert({name: "Homer"})
> db.so.employee.find()
{ "_id" : ObjectId("4de35ccbcc0379536e1ac43b"), "name" : "Joe" }
{ "_id" : ObjectId("4de35ccfcc0379536e1ac43c"), "name" : "Moe" }
{ "_id" : ObjectId("4de35cd3cc0379536e1ac43d"), "name" : "Bart" }
{ "_id" : ObjectId("4de35cd7cc0379536e1ac43e"), "name" : "Homer" }

Now create some projects

> db.so.project.insert({name: "Web App A"})
> db.so.project.insert({name: "Web App B"})
> db.so.project.insert({name: "Web App C"})
> db.so.project.find();
{ "_id" : ObjectId("4de35d0fcc0379536e1ac43f"), "name" : "Web App A" }
{ "_id" : ObjectId("4de35d13cc0379536e1ac440"), "name" : "Web App B" }
{ "_id" : ObjectId("4de35d15cc0379536e1ac441"), "name" : "Web App C" }

Add the users to the projects

> db.so.project.update({name: "Web App A"}, {$push: {employees: ObjectId('4de35ccbcc0379536e1ac43b') }})
> db.so.project.update({name: "Web App A"}, {$push: {employees: ObjectId('4de35ccfcc0379536e1ac43c') }})
> db.so.project.update({name: "Web App B"}, {$push: {employees: ObjectId('4de35ccfcc0379536e1ac43c') }})
> db.so.project.update({name: "Web App C"}, {$push: {employees: ObjectId('4de35ccfcc0379536e1ac43c') }})
> db.so.project.update({name: "Web App B"}, {$push: {employees: ObjectId('4de35cd3cc0379536e1ac43d') }})
> db.so.project.update({name: "Web App C"}, {$push: {employees: ObjectId('4de35cd3cc0379536e1ac43d') }})
> db.so.project.update({name: "Web App B"}, {$push: {employees: ObjectId('4de35cd7cc0379536e1ac43e') }})

> db.so.project.find()
{ "_id" : ObjectId("4de35d0fcc0379536e1ac43f"), "employees" : [
    ObjectId("4de35ccbcc0379536e1ac43b"),
    ObjectId("4de35ccfcc0379536e1ac43c")
], "name" : "Web App A" }
{ "_id" : ObjectId("4de35d15cc0379536e1ac441"), "employees" : [
    ObjectId("4de35ccfcc0379536e1ac43c"),
    ObjectId("4de35cd3cc0379536e1ac43d")
], "name" : "Web App C" }
{ "_id" : ObjectId("4de35d13cc0379536e1ac440"), "employees" : [
    ObjectId("4de35cd3cc0379536e1ac43d"),
    ObjectId("4de35cd7cc0379536e1ac43e")
], "name" : "Web App B" }

If you now want to find all projects on which works "Joe"

> db.so.project.find({employees: ObjectId('4de35ccbcc0379536e1ac43b') }, {name: 1})
{ "_id" : ObjectId("4de35d0fcc0379536e1ac43f"), "name" : "Web App A" }

Find all projects on which Joe OR Moe works

> db.so.project.find({employees: {$in: [ObjectId('4de35ccbcc0379536e1ac43b'), ObjectId('4de35ccfcc0379536e1ac43c')] }}, {name: 1})
{ "_id" : ObjectId("4de35d0fcc0379536e1ac43f"), "name" : "Web App A" }
{ "_id" : ObjectId("4de35d15cc0379536e1ac441"), "name" : "Web App C" }
{ "_id" : ObjectId("4de35d13cc0379536e1ac440"), "name" : "Web App B" }

Find all project on which Joe AND moe works

> db.so.project.find({employees: {$all: [ObjectId('4de35ccbcc0379536e1ac43b'), ObjectId('4de35ccfcc0379536e1ac43c')] }}, {name: 1})
{ "_id" : ObjectId("4de35d0fcc0379536e1ac43f"), "name" : "Web App A" }

To get all employess for a specific project you need two queries. Finding all employees from Project C.

> db.so.project.find({name: "Web App C"}, {employees: 1})
{ "_id" : ObjectId("4de35d15cc0379536e1ac441"), "employees" : [
    ObjectId("4de35ccfcc0379536e1ac43c"),
    ObjectId("4de35cd3cc0379536e1ac43d")
] }

In your Appllication you build a new query from the returning values and build this query:

> db.so.employee.find({_id: {$in: [ObjectId('4de35ccfcc0379536e1ac43c'), ObjectId('4de35cd3cc0379536e1ac43d')] }})
{ "_id" : ObjectId("4de35ccfcc0379536e1ac43c"), "name" : "Moe" }
{ "_id" : ObjectId("4de35cd3cc0379536e1ac43d"), "name" : "Bart" }

I hoped that helped to understand how MongoDB works and how you could build relations. I used manual dereferencing here. That means i save the ObjectID directly and fetch it manually. There also exists "DBRef" and your Driver then fetch it for you.

Is there a way to "join" the employees in the project query, so that you can combine the last two queries into one? — FrederickCook, Jul 30 '11 at 18:13
No, MongoDB has no "joins", this is a "feature". You need to do it on the client. In your software. — David Raab, Jul 30 '11 at 22:50

score 0 · Answer 3 · edited May 23 '17 at 10:00

What you are looking for is effectively an N:M mapping. In this case you're trying to map a table to itself, but this is not very different from trying to map "Employees" to "Projects".

There's a long answer over here on SO that I won't repeat here.

In your specific case, I think you need to re-design your data a little. You have an assignments table containing two data points: projectID and employeedID. This is a classic table for joining N projects to M employees.

In MongoDB, you generally don't have this "table" at all. If you want to assign employees to a project, you can just store an array of employeeID in the Project itself.

{ name: 'projectx', emps: [ 1, 2, 3] }
{ name: 'projecty', emps: [ 3, 2] }

It looks like your query is basically "find all projects where employee 2 and 3 are working together". MongoDB has an $all query operator that will do this for you with the above structure.

Please note that this is just one query. It's important to look at your whole system and identify all important queries when doing schema design.

MongoDB/CouchDB: Join a table to itself

3 Answers3