Relations between mongo collections

In MongoDB there are relationships between collections. What happens is that Mongo is a noSQL database . This means that it does not use Structured Query Language (or SQL in English). Therefore, there is no sentence like SELECT * FROM table, since it is typical of that language.

On the other hand, the term " non-relational database" tends to be confused with the absence of relationships. The truth is that Mongo does allow you to implement relationships, but through something known as references . There is even calm talk of cardinality of relations, with the same characteristics that we know in a "relational" database. The particularity of Mongo (as well as many other noSQL DBs ) is that it does not fit or adhere faithfully to the ER (Entity-Relationship) model, hence its description as a non-relational DB .

ISSUE

You want to create a relationship between two Mongo collections. One collection, called users , will have a relationship with another collection called messages . Each type document is required to messagehave a reference in two of its fields to a type document user.

SOLUTION

To achieve what we propose, we must create a reference to userin each field of the type document messagewhere we want to create the relationship. Mongo offers us two types of References

Manual References

They are the ones used by placing the value of _ida document in the field of another document. Then, if you want to obtain the associated document _id, you must make a second query to the database using the information of said value.

Suppose we create the collection usersas follows:

> db.users.insert([{"name": "Mauricio", "email": "[email protected]"},{"name": "Patricio", "email": "[email protected]"}])

If we want to see the objects of the collection usersthat we just created, we will use the method find():

> db.users.find({})
{ "_id" : ObjectId("5c9ccc140aee604c4ab6cd06"), "name" : "Mauricio", "email" : "[email protected]" }
{ "_id" : ObjectId("5c9ccc140aee604c4ab6cd07"), "name" : "Patricio", "email" : "[email protected]" }

We can see that Mongo has assigned the values _id​​as type ObjectId. It does this automatically unless we _idexplicitly pass it a value for the field.

Now we will create the collection messagesand enter the reference manually. We achieve this in the following way:

> var idMauricio = ObjectId("5c9ccc140aee604c4ab6cd06")
> var idPatricio = ObjectId("5c9ccc140aee604c4ab6cd07")
> db.messages.insert([{"idFrom": idMauricio, "idTo": idPatricio, "message": "¿Como estas?"},{"idFrom": idPatricio, "idTo": idMauricio, "message": "Muy bien, gracias"}])

(we have used the corresponding values _id)

If we want to see the documents in the collection, messageswe use the method again find():

> db.messages.find()
{ "_id" : ObjectId("5c9cce340aee604c4ab6cd08"), "idFrom" : ObjectId("5c9ccc140aee604c4ab6cd06"), "idTo" : ObjectId("5c9ccc140aee604c4ab6cd07"), "message" : "¿Como estas?" }
{ "_id" : ObjectId("5c9cce340aee604c4ab6cd09"), "idFrom" : ObjectId("5c9ccc140aee604c4ab6cd07"), "idTo" : ObjectId("5c9ccc140aee604c4ab6cd06"), "message" : "Muy bien, gracias" }

Now we have manually related documents in the collection messagesto documents in the collection users.

Many times these types of manual relationships will suffice for our application. However, to know the name or email of the user who sent the message or the user who received the message, we must make a second query to the database:

> var cursor = db.messages.find({"_id" : ObjectId("5c9cce340aee604c4ab6cd08")},{"_id":0, "idFrom": 1})
> var message = cursor.hasNext() ? cursor.next() : null
> if (message) {
... var id = message.idFrom
... }
> db.users.find({"_id": id})
{ "_id" : ObjectId("5c9ccc140aee604c4ab6cd06"), "name" : "Mauricio", "email" : "[email protected]" }

In the previous query I have used a query( {"_id" : ObjectId("5c9cce340aee604c4ab6cd08")}) in the method find(), and I have also passed a projection( {"_id":0, "idFrom": 1}) as a parameter.

If we wanted to do a similar query in SQL, it could be written like this:

SELECT idFrom FROM messages WHERE id = "5c9cce340aee604c4ab6cd08"

You can find more information about the method find()in the documentation

DBRef References

DBRefs is a convention used to represent a document, rather than a specific (manual) reference type like the one we just saw. References of this type include the name of the collection to which the document belongs, the value of_idthe document field, and may include the name of the database to which the collection belongs.

A document DBRefhas the following format: (it is important to respect the order of the fields)

{"$ref": <value>, "$id": <value>, "$db": <value>}

To use a type reference DBRefin our case we can do it as follows:

> db.messages2.insert([{"idFrom": {"$ref": "users", "$id": ObjectId("5c9ccc140aee604c4ab6cd07")}, "idTo": {"$ref": "users", "$id": ObjectId("5c9ccc140aee604c4ab6cd06")}, "message": "Gracias por responder"},{"idFrom": {"$ref": "users", "$id": ObjectId("5c9ccc140aee604c4ab6cd06")}, "idTo": {"$ref": "users", "$id": ObjectId("5c9ccc140aee604c4ab6cd07")}, "message": "Un gusto ayudarte"}])

If we make a query to our collection messages2we obtain the following:

> db.messages2.find()
{ "_id" : ObjectId("5c9cd7e3b6e8c62ece6b9981"), "idFrom" : DBRef("users", ObjectId("5c9ccc140aee604c4ab6cd07")), "idTo" : DBRef("users", ObjectId("5c9ccc140aee604c4ab6cd06")), "message" : "Gracias por responder" }
{ "_id" : ObjectId("5c9cd7e3b6e8c62ece6b9982"), "idFrom" : DBRef("users", ObjectId("5c9ccc140aee604c4ab6cd06")), "idTo" : DBRef("users", ObjectId("5c9ccc140aee604c4ab6cd07")), "message" : "Un gusto ayudarte" }

In this way we have created the relationship between the collection messages2and the collection usersusing DBRefs.

Again, to know the name or email of the user that sends or receives, a second query must be made to the database

JOIN

Now, since the question asks if JOINs can be done in Mongo, the answer is: YES, using a method called aggregate(), which as its translation supposes, adds something to our query. In this case we can add the documents associated with the in the e _idfields of the collection . This type of is known as . This way we have a way to fetch the data in a single query.idFromidTomessagesaggregation$lookup aggregation

A simple example of this can be seen in the following query:

> db.messages.aggregate([ {$lookup: {from: "users", localField: "idFrom", foreignField: "_id", as: "sender" }}, {$lookup: {from: "users", localField: "idTo", foreignField: "_id", as: "receiver" }}])
{ "_id" : ObjectId("5c9cce340aee604c4ab6cd08"), "idFrom" : ObjectId("5c9ccc140aee604c4ab6cd06"), "idTo" : ObjectId("5c9ccc140aee604c4ab6cd07"), "message" : "¿Como estas?", "sender" : [ { "_id" : ObjectId("5c9ccc140aee604c4ab6cd06"), "name" : "Mauricio", "email" : "[email protected]" } ], "receiver" : [ { "_id" : ObjectId("5c9ccc140aee604c4ab6cd07"), "name" : "Patricio", "email" : "[email protected]" } ] }
{ "_id" : ObjectId("5c9cce340aee604c4ab6cd09"), "idFrom" : ObjectId("5c9ccc140aee604c4ab6cd07"), "idTo" : ObjectId("5c9ccc140aee604c4ab6cd06"), "message" : "Muy bien, gracias", "sender" : [ { "_id" : ObjectId("5c9ccc140aee604c4ab6cd07"), "name" : "Patricio", "email" : "[email protected]" } ], "receiver" : [ { "_id" : ObjectId("5c9ccc140aee604c4ab6cd06"), "name" : "Mauricio", "email" : "[email protected]" } ] }

Basically we are telling Mongo to lookup the documents in the collection userswhose _idmatches the field idFrome idToand add them to the returned document in a field senderand a field receiver.

This translated to SQL is a simple JOIN, or maybe a query of the type:

SELECT *, sender, receiver 
FROM messages 
WHERE sender IN (SELECT * FROM users WHERE id = messages.idFrom)
AND receiver IN (SELECT * FROM users WHERE id = messages.idTo)

As you can see, almost everything that can be done with SQL can be done in Mongo.

Hope this is the answer you were looking for.