0

I used to have two tables in SQL Server:

Branch(branchNo, city)
Staff(branchNo, staffNo, staffName)

On the SQL Server, I could find all staff in a specific city using:

select staffName from Branch, Staff where
Branch.branchNo == Staff.branchNo AND city = 'a specific city'

Now in mongoDB, I put both tables' content into one collection, how can I use MapReduce to get the same effect of the sql query?

geyan
  • 11
  • Could you show structure of your MongoDB collection? – Andrei Beziazychnyi May 30 '14 at 06:40
  • That is called a join. MongoDB does not do joins you do things differently instead. Take a look at the documentation section on [Data Modelling](http://docs.mongodb.org/manual/data-modeling/) from the MongoDB site. – Neil Lunn May 30 '14 at 06:41
  • Some rows are like(BranchNo:"B001", city:"NYC") and some other rows are like (BranchNo:"B001", staffNo:"s001", staffName:"John") – geyan May 30 '14 at 06:41

1 Answers1

1

You did the data import wrong. Really take a look at the MongoDB Docs regarding data modelling that Neil linked to. But I know MongoDB can be confusing when you're coming from relational database background.

I propose that you join the data when exporting 

SELECT staffNo, staffName, branchNo, city FROM Branch, Staff 
WHERE Branch.branchNo = Staff.branchNo

and put that into MongoDB documents like this:

{staffNo: "s001", staffName: "John", BranchNo: "B001", city:"NYC"}

(Not sure if the branchNo is meaningful or was only needed for joining the two tables. In the latter case you could leave it out).

Then your query to find all staff in one city would just be

db.collection.find({"city": "NYC"})
mthierer
  • 594
  • 4
  • 9
  • Putting all attributes in a row is convenient for queries, but there will also be huge impact on the disk space consumed, as they will be significantly more rows than original. how to solve that problem? – geyan May 30 '14 at 07:43
  • Well, yes, that's kind of the trade-off you have to make when using a document oriented instead of a relational database. OTOH I doubt the impact will be "huge" in your example given that it's just one more field for the main table and you don't need the branchNo / city table. If you're **really** concerned about that (you shouldn't be) you **can** split up the data in multiple collections in MongoDB as well - you just have to do the joints yourself. See the post that Neil linked to in his second comment. – mthierer May 30 '14 at 08:14