如何使用mongify将两个表合并为一个表

I am planning to migrate my application database from Mysql to Mongo with low schema changes. In my new schema I merged two Mysql tables into one Mongo collection. I want to use mongify (https://github.com/anlek/mongify) gem to populate my existing Mysql data into Mongo with newer schema.

我打算使用低架构更改将我的应用程序数据库从Mysql迁移到Mongo。在我的新模式中，我将两个Mysql表合并为一个Mongo集合。我想使用mongify（https://github.com/anlek/mongify）gem将我现有的Mysql数据填充到Mongo中，使用更新的模式。

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

这个怎么做？有没有办法在mongify中将两个Mysql表合并为一个？

Mysql tables

Mysql表

user
    id
    name
    nickname
    type

user_role
    id
    role
    alias
    user_id

I merged the above two tables into single collection in Mongo

我将以上两个表合并为Mongo中的单个集合

user
    id
    name
    type
    role
    alias

3 个解决方案

#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 that data to sql format and upload it to mongodb

将该数据导出为sql格式并将其上传到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 might help you

我想它可能对你有所帮助

#2

Fetch the data using JOIN from MySQL and load that data into MongoDB:

从MySQL使用JOIN获取数据并将该数据加载到MongoDB中：

Try this:

尝试这个：

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

#3

An alternative if its difficult to merge two tables ad-hoc then run a mongo shell script post-hoc; they are very easy to program (couple o' lines of js) and simple to execute. plus one can apply any needed conversion, such as into json/bson arrays permitted in mongo but not the mysql.

如果难以合并两个表ad-hoc然后运行mongo shell脚本post-hoc;它们很容易编程（连接j行）并且易于执行。加一个可以应用任何所需的转换，例如进入mongo允许的json / bson数组，但不能应用于mysql。

assuming a user and user_role collection (or table) in mongo.

假设mongo中有user和user_role集合（或表）。

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 the mongo shell

然后使用mongo shell执行

mongo localhost:27017/test myjsfile.js

mongo localhost：27017 / test myjsfile.js

#1