How to combine two tables into one using mongify

Question

I plan to migrate my application database from Mysql to Mongo with minor schema changes. In my new schema, I combined two Mysql tables into one Mongo collection. I want to use mongify ( https://github.com/anlek/mongify ) to populate my existing Mysql data in Mongo with a new schema.

How to do it? Is there a mongify way to merge two Mysql tables into one?

Mysql Tables

user id name nickname type user_role id role alias user_id 

I combined these two tables into one collection in Mongo

 user id name type role alias 

Answer 1

Try Left Join: (Tables will be merged)

 SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name; 

Export this data to sql format and upload it to mongodb

 SELECT country INTO customerCountry FROM customers WHERE customerNumber = p_customerNumber; CASE customerCountry -- Here you have to see if that alias data is set WHEN 'USA' THEN SET p_shiping = '2-day Shipping'; -- here you have to write Update Query ELSE SET p_shiping = '5-day Shipping'; END CASE; 

I think it can help you.

Answer 2

Extract data using JOIN from MySQL and load this data into MongoDB:

Try the following:

 SELECT U.id, U.name, U.type, UR.role FROM USER U INNER JOIN user_role UR ON U.id = UR.user_id; 

Answer 3

Alternatively, if it is difficult to join two ad-hoc tables, then run the mongo shell script post-hoc; they are very easy to program (a couple of "lines" of js) and easy to execute. plus you can apply any necessary conversion, for example, to json / bson arrays allowed in mongo, but not mysql.

provided that the user and user set (or table) are in mangoes.

 db.user_role.find({}).forEach(function(doc) { var roles = doc.roles; // convert mysql format here if needed. //var roles_array = JSON.parse(roles) // or whatever? //var alias = doc.alias; // if alias is null empty or ?? // dont upsert to avoid orphaned user_role records db.user.update({"_id": doc.user_id}, {"roles": roles, "alias", doc.alias }, {upsert: false, multi: false}); } 

Then execute using mongo shell

mongo localhost: 27017 / test myjsfile.js