Question:
I'm starting in Mongodb
, and as you know it has no relaciones
, no tablas
joins
etc.
I'm trying to pass a relational diagram to NOSQL
. I have the following tables.
users
id
name
messages
idUserFrom
idUserTo
messages
As you can see, the messages
table has the users
id as foreigners, how can I create it, although there are no pks
and fks
in mongo, how can I perform this step.
So far I have created the database and a collection
with the insertion of the users
table.
> use mongodb
switched to db mongodb
> db
mongodb
> db.users.insert({id:"1",name:"Jorge"})
WriteResult({ "nInserted" : 1 })
> show collections
users
> db.users.find().pretty();
{
"_id" : ObjectId("5c9bbc275a56aa679bb06155"),
"id" : "1",
"name" : "Jorge"
}
How can I make it look like this, this being the idFrom
collections
: This table contains the id of some user, which can be the messages
or idTo
db.messages.find().pretty();
{
"_id" : ObjectId("5c9bbc275a56aa679bb06155"),
"idFrom" : "xxxxxxxxxx",
"idTo" : "xxxxxxxxxx",
"message" : "Hola"
}
Answer:
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 statement like SELECT * FROM table
, since it is specific to 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.
TROUBLE
You want to create a relationship between two Mongo collections. One collection, called users , will have a relationship with another collection called messages . Each document type message
is required to have a reference in two of its fields to a document type user
.
SOLUTION
To achieve what we are proposing, we must create a reference to user
in each field of the message
type document where we want to create the relationship. Mongo offers us two types of References
Manual References
They are the ones used by placing the _id
value of a document in the field of another document. Then, if you want to obtain the document associated with the _id
, you must make a second query to the database using the information of said value.
Suppose we create the users
collection as follows:
> db.users.insert([{"name": "Mauricio", "email": "mauricio@email.com"},{"name": "Patricio", "email": "patricio@email.com"}])
If we want to see the objects of the users
collection that we just created, we will use the find()
method:
> db.users.find({})
{ "_id" : ObjectId("5c9ccc140aee604c4ab6cd06"), "name" : "Mauricio", "email" : "mauricio@email.com" }
{ "_id" : ObjectId("5c9ccc140aee604c4ab6cd07"), "name" : "Patricio", "email" : "patricio@email.com" }
We can see that Mongo has assigned the _id
values as type ObjectId
. It does this automatically unless we explicitly pass it a value for the _id
field.
We will now create the messages
collection and 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 _id
values)
If we want to see the documents in the messages
collection, we use the find()
method again:
> 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" }
We now have documents in the messages
collection manually related to documents in the users
collection.
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" : "mauricio@email.com" }
In the previous query I have used a query
( {"_id" : ObjectId("5c9cce340aee604c4ab6cd08")}
) in the find()
method, and I have also passed a projection
as a parameter ( {"_id":0, "idFrom": 1}
).
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 find()
method 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 the document's _id
field, and may include the name of the database to which the collection belongs.
A DBRef
has the following format: (it is important to respect the order of the fields)
{"$ref": <value>, "$id": <value>, "$db": <value>}
To use a DBRef
type reference in 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 messages2
collection we get 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 messages2
collection and the users
collection using DBRefs
.
Again, to know the name or email of the user who 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 _id
in the idFrom
and idTo
fields of the messages
collection. This type of aggregation
is known as a $lookup aggregation
. This way we have a way to fetch the data in a single query.
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" : "mauricio@email.com" } ], "receiver" : [ { "_id" : ObjectId("5c9ccc140aee604c4ab6cd07"), "name" : "Patricio", "email" : "patricio@email.com" } ] }
{ "_id" : ObjectId("5c9cce340aee604c4ab6cd09"), "idFrom" : ObjectId("5c9ccc140aee604c4ab6cd07"), "idTo" : ObjectId("5c9ccc140aee604c4ab6cd06"), "message" : "Muy bien, gracias", "sender" : [ { "_id" : ObjectId("5c9ccc140aee604c4ab6cd07"), "name" : "Patricio", "email" : "patricio@email.com" } ], "receiver" : [ { "_id" : ObjectId("5c9ccc140aee604c4ab6cd06"), "name" : "Mauricio", "email" : "mauricio@email.com" } ] }
We're basically telling Mongo to lookup the documents in the users
collection whose _id matches the _id
and idFrom
idTo
and add them to the returned document in a sender
field and a receiver
field.
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.