Reference collections in mongodb

Asked

Viewed 794 times

1

I have a model of an airport in mysql and I intend to upload it to mongodb via links between collections. In the following images are examples of documents from my collections. (Airplane, Airport and Flight)

flight

Airport

Airplane

The id in Flight is the id_voo, in Airplane is the tailnum and in Airport is idAirport. In mysql the relations are 1:M from Airplane to Flight and 1:M from Flight to airport. I exported this data with foreign keys and I want to cross-reference it between documents in mongodb. Mongo db immediately assigns an objectid as the main one and that’s not what it intended. How can I set this?

mysql mongodb

  • That is, you’re intending to make one Join with Mongo?

  • Yeah, kind of like that. wanted a way to make a querie that would return me the year of a plane but that where the field diverted was equal to 1. only this field diverted is in the Flight collection

  • So man, I’m sorry to tell you but Mongo doesn’t have the resources to do joins. The paradigm is another. See the answer of the sergiopereira.

1 answer

2

As you know, Mongodb is not relational, so there is no way to define a native relationship between two documents. You have two alternatives most common in Mongo:

  1. Built-in documents (nested)
  2. Manual reference, using _id

I recommend you read a little about this topic because it is a long way to explain here on the site. Each one has its advantages and disadvantages.

Regarding the primary key, in Mongodb this is always the attribute _id and is unchanging. You have to either start using _id in your programs, putting yourself the value of _id at the time of insertion, otherwise Mongo will generate one for you. The _id does not need to be a simple value, it can be a sub-document as well, as "_id": {"numero": 123, "apartamento": "25-B"}.

  • so if in the Json I want to enter the name is _id it automatically inserts it with the main id of the document? Regarding the manual reference I have already researched about this and my question is whether it is enough to load the json with the foreign keys that correspond to the primary keys of the tables but without any kind of relation is only for the id’s to match in mongodb?

  • With regard to _ids, correct. For Fks you just add the fields with the correct values. Your examples seem correct.

  • If I choose to use built-in documents, for example I already have the Airplane collection created and I want to embed the Flight collection inside it but where the tailnum are the same, how do I create a document like this?

  • 1

    You’ll need to repeat the data, like _id: 4, airplane: {tailnum: "N308UA", model: "737-3TO", ..etc.. } }. Basically the airplane becomes embedded in the flight, which will cause data duplication. In some cases this is acceptable, in others it will not, will depend on your analysis and knowledge of the domain of the problem.

  • Exactly why I thought in the referencing that so there was no duplication. My confusion is even to have to make 2 queries. What is your advice for such a situation?

  • There is no magic, unfortunately. You will need to either duplicate data or do more than one query to fetch the related data.

  • By the way. Do you have any important reason to store this data in Nosql? The data seems typical of relational databases.

  • yes it is a work that I am doing comparing mysql with mongodb, these data already come from mysql, I simply imported the ones in json, now I see what is the type of storage that gives me better performance if the embedded or the referenced one. Do you have any idea which one will compensate the most in performance in this case?

Show 3 more comments

Browser other questions tagged mysql mongodb

You are not signed in. Login or sign up in order to post.