CODEWITHSUNDEEP
mysqlmongodbdatabase-designdatabase-migration
Begayim Muratalina
Begayim Muratalina

Reputation: 661

mongodb schema design for storing mysql column like array value query

I've mysql database who looks like this :

    ----|-----------|--------|------------|---------|----------|---------|----------|----------|
   _id| Segment_ID| Origin | Destination| Aircraft| Latitude1|Latitude2|Longitude1|Longitude2|
    ----|-----------|--------|------------|---------|----------|---------|----------|----------|
     1  | LFAQ-GILTO| LFAQ   | KURIS      | A3ST    | 49.56    | 2.5     | 49.97    | 2.33     |
    ----|-----------|--------|------------|---------|----------|---------|----------|----------|
   1    |GILTO-EVX  | LFAQ   | KURIS      | A3ST    | 49.97    | 2.33    | 50.70    | 2.12     |
    ----|-----------|--------|------------|---------|----------|---------|----------|----------|
    1   |EVX-KURIS  | LFAQ   | KURIS      | A3ST    | 50.70    | 2.12    | 52.00    | 2.07     |
    ----|-----------|--------|------------|---------|----------|---------|----------|----------|

i want to migrate my database from mysql to mongodb. i use mongify to migrate my database in embedded collection in mongodb. i had no problems with that. BUT, i want to do the next: 1. migrate to embedded collection in mongodb and put the columns as the array value. 2. i want my mongodb collection look like this

Flight: { 
  id:1 {
  "Segment_ID": "LFAQ-GILTO, GILTO-EVX, EVX-KURIS",
  "Origin": "LFAQ, LFAQ, LFAQ",  
  "Destination": "KURIS, KURIS, KURIS",
  "Aircraft" : "A3ST, A3ST, A3ST",
  "Latitude1" : "49.56, 49.97, 50.70",
etc.....

Is there any way i can make it work?

for now my database looks like:

Flight: {
id1: {
"0": {
"Segment_ID": "LFAQ-GILTO",
"Origin": "LFAQ",
"Destination": KURIS",
"Aircraft" : "A3ST",
"LAtitude1": "49.56",
.......
},
"1": {
"Segment_ID": "LFAQ-GILTO",
"Origin": "LFAQ",
"Destination": KURIS",
"Aircraft" : "A3ST",
"LAtitude1": "49.56",
.....
},
"2": {
"Segment_ID": "LFAQ-GILTO",
"Origin": "LFAQ",
"Destination": KURIS",
"Aircraft" : "A3ST",
"LAtitude1": "49.56",
.....},
......

Upvotes: 0

Views: 646

Answers (1)

HILARUDEEN S ALLAUDEEN
HILARUDEEN S ALLAUDEEN

Reputation: 1752

Your MongoDB structure is good to look when compare to MySql. But if you are keep the information like this, you have to do loop on reading data. If data size grow large, this loops will slow down your application.

For example, You want rows where "Segment_ID" is "GILTO-EVX", In MongoDB, you have loop data against "Segment_ID" array. Because MongoDB is return Parent Key and all of its values, even single value match with your condition. In above case, MongoDB will give you

{ "Segment_ID": ["LFAQ-GILTO", "GILTO-EVX", "EVX-KURIS", ....] } //If you are keep values in array
{ "Segment_ID": "LFAQ-GILTO, GILTO-EVX, EVX-KURIS, ....." } //If you are keep values as string with comma seperated

Both are difficult to use further process. I mean you have to keep bigger array or bigger string in your variable. This complex will increase, on reading respective information on other Keys(column) with respective to "Segment_ID = GILTO-EVX". Here my suggestion is restructure your MongoDB docs as follows,

//Doc 1
{ 
  "Flight_id" : 1,
  "Segment_ID": "LFAQ-GILTO",
  "Origin": "LFAQ",  
  "Destination": "KURIS",
  "Aircraft" : "A3ST",
}
//Doc 2
{ 
  "Flight_id" : 1,
  "Segment_ID": "GILTO-EVX",
  "Origin": "LFAQ",  
  "Destination": "KURIS",
  "Aircraft" : "A3ST",
}
//Doc3 and more...

Upvotes: 1

Related Questions