"aggregate" (model and query) three collections?

Working with Data
, ,
#1

I am new to NoSQL and I am trying mongodb. I came from a relational database background and I need to be able to join or aggregate rows from 3 collections. The Student collection contains IDNum, Name, etc. the Books collection contains ISBN, Title, Edition, etc. and the ReservedBooks collection contains the IDNum of the student and the ISBN of the book.

Here are my inquiries:
-How would I go about retrieving all the reserved books of a certain student?
-Can I return all students with reserved books?
-Do I need to modify my student collection such that it will contain an array which will store the ISBNs of the reserved books?

Thank you in advance!

1 Like
#2

Hello @dee_u, welcome to the MongoDB Community forum!

There are different ways you can model your data in MongoDB. This mostly depends upon aspects like how you want to query the data in your application, the amount of data, and the relationships between entities.

Here is a way to design your data and query.

students:
{ idNum: 12, name: "John", email: "john@example.com" }

books:
{ isbn: "1234-5678", title: "Huckleberry Finn", author: "Mark Twain", reservedBy: "John" }

To query all books reserved by a student:

db.books.find( { reservedBy: "John" } )

To query all students with reserved books:

db.books.aggregate([
  { $match: { reservedBy: { $ne: "" } } },
  { $group: { _id: { studentName: "$reservedBy" }, booksReserved: { $push: "$title" } } }
])

References:

1 Like
#3

@Prasad_Saya Thank you so much for the response, I appreciate it. I really like your suggestion, I could just store in the books collection the IDNum of the student that reserved it. I am still wrapping my head on how to store things in MySQL, it is way different different to RDBMS.

1 Like
#4

@dee_u, if you are familiar with RDBMS and SQL programming, you can refer this documentation for comparing related terminology and know how the queries are formed from SQL.

2 Likes
#5

That’s awesome! This is one of the resources referenced in the course M100: MongoDB for SQL Pros that I just finished right before I read your post! Talk about solidifying the source and great timing. Thanks for guiding us out here.