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.