Relationship between 2 collections in MongoDB and show data between them

Question

I am making a small database for library with MongoDB. I have 2 collections, first one is called 'books' which stores information about books. The second collection is called 'publishers' which stores information about the publishers and the IDs of the books which they published.

This is the document structure for 'books'. It has 3 documents

{
    "_id" : ObjectId("565f2481104871a4a235ba00"),
    "book_id" : 1,
    "book_name" : "C++",
    "book_detail" : "This is details"
},

{
    "_id" : ObjectId("565f2492104871a4a235ba01"),
    "book_id" : 2,
    "book_name" : "JAVA",
    "book_detail" : "This is details"
},
{
    "_id" : ObjectId("565f24b0104871a4a235ba02"),
    "book_id" : 3,
    "book_name" : "PHP",
    "book_detail" : "This is details"
}

This is the document structure for 'publishers'. It has 1 document.

{
    "_id" : ObjectId("565f2411104871a4a235b9ff"),
    "pub_id" : 2,
    "pub_name" : "Publisher 2",
    "pub_details" : "This is publishers details",
    "book_id" : [2,3]
}

I want to write a query to show all the details of the books which are published by this publisher. I have written this query but it does not work. When I run it, it displays this message "Script executed successfuly, but there are no results to show.".

db.getCollection('publishers').find({"pub_id" : 2}).forEach(
      function (functionName) {
      functionName.books = db.books.find( { "book_id": functionName.book_id } ).toArray();
      } 
)

Possible duplicate of [How do I perform the SQL Join equivalent in MongoDB?](http://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb). tl;dr: use aggregation with a [$lookup](https://docs.mongodb.org/master/reference/operator/aggregation/lookup/#pipe._S_lookup) stage — Philipp, Dec 03 '15 at 17:05

score 2 · Accepted Answer · answered Dec 03 '15 at 17:11

I think that your data structure is flawed. The publisher is a property of a book, not the other way around. You should add pub_id to each book, and remove book_id from the publisher:

{
    "_id" : ObjectId("565f2481104871a4a235ba00"),
    "book_id" : 1,
    "book_name" : "C++",
    "book_detail" : "This is details",
    "pub_id" : 1
},
{
    "_id" : ObjectId("565f2492104871a4a235ba01"),
    "book_id" : 2,
    "book_name" : "JAVA",
    "book_detail" : "This is details"
    "pub_id" : 2
},
{
    "_id" : ObjectId("565f24b0104871a4a235ba02"),
    "book_id" : 3,
    "book_name" : "PHP",
    "book_detail" : "This is details"
    "pub_id" : 2
}

Then, select your books like such:

db.getCollection('books').find({"pub_id" : 2});

score 1 · Answer 2 · answered Dec 03 '15 at 16:40

1

Try this way,

db.getCollection('publishers').find({"pub_id" : "2"}).exec(function(err, publisher){
        if (err) {
            res.send(err);
        }
        else
            if(publisher)
            {

                publisher.forEach(function(functionName)
                {

                   functionName.books = db.books.find( { "book_id": functionName.book_id } ).toArray();
                })

            }
    })

answered Dec 03 '15 at 16:40

Afroza Yasmin

511
1
4
22

I run it and it gives me this error 'TypeError: db.getCollection("publishers").find({pub_id:"2"}).exec is not a function (shell):1' – Speenzar XaXai Dec 03 '15 at 17:02
Then you got bulk operation in mongoDB & does not find any exec function. So lets try as your way, db.getCollection('publishers').find({"pub_id" : "2"}). forEach(function(functionName) { functionName.books = db.books.find( { "book_id": functionName.book_id } ).toArray(); }) – Afroza Yasmin Dec 03 '15 at 17:13

score 0 · Answer 3 · answered Dec 04 '15 at 22:32

I would suggest reading the official documentation, because the relationship between books and publishers is precisely the example which is used there: https://docs.mongodb.org/manual/tutorial/model-referenced-one-to-many-relationships-between-documents/

In mongoDB and noSQL at large, it is not true that publisher must be a property of book. This is only the case in RDBMS, where in one-to-many relationships the reference is in the "one" part. The same works the other way round, books don't have to be a property of publisher. The clue here is in the absence of "must."

It all depends on how many is the "many-to-many". In this case, I'd say it's also about what type of library we're talking about, size of catalogue and whether new book purchases are common:

Is the number of books per publisher small AND data about publisher is often accessed with data about the book? Then, embed publisher info in the book document.
Is the number of books per publisher fairly big but reasonably stable (e.g.: historical library where acquisitions are rare)? Then, create a publishers collection with an array of books per publisher.
Is the number of books per publisher fairly big and catalogue grows at a reasonable pace? Then, include reference in the book document and fetch publisher info with it.

Side note

Although not related to the question, I think your document structure is flawed. _id and book_id are redundant. If you want to follow the RDBMS pattern of incremental integer IDs, then it's absolutely OK that you specify your own _id at the time of inserting the document with 1, 2, 3, etc. ObjectID() is a great thing, but, again, there's no obligation to use it.

Relationship between 2 collections in MongoDB and show data between them

3 Answers3