mysql - 将 MySQL 数据库导入 MongoDB - 一对多关系 ID 更新

Question

我使用 MongoVUE 将我的 MySQL 数据库导入到 MongoDB。

首先让我举一个表格的例子:

Table0 = 1,500 entries
Table1 = 120,000 entries
Table2 = 18,000,000 entries

Table0 -> hasMany -> Table1 entries
Table1 -> hasMany -> Table2 entries

所有表现在都有一个 _id 键，但在导入后两个表仍然有一个来自 MySQL 的 id 键。

如何更新表 2 的键 table1_id 以匹配表 1 的 _id 键？使用 Mongo 查询是否可行，还是我必须为此编写脚本？ (我唯一会的语言是 PHP 和 Javascript/NodeJS)

---

更新1

使用用户@profesor79 回答我做了这个查询，其中 table1 = market_item_histories 和 table2 = market_items

db.market_item_histories.aggregate([    
    {
        $lookup: {
            from:"market_items",
            localField: "market_item_id",
            foreignField: "id",
            as: "market_items_docs"
        }
    },
    {
        $unwind:"$market_items_docs"
    },
    {
        $project: {
            _id:1,
            oldId:"$market_item_id",
            market_item_id:"$market_items_docs._id",
            date:1,
            price:1,
            amount:1,
            created_at:1,
            updated_at:1
        }        
    },
    {
        $out:"marketItemHistories"
    }
])

运行该代码时出现此错误:

assert: command failed: {
        "errmsg" : "exception: Unrecognized pipeline stage name: '$lookup'",
        "code" : 16436,
        "ok" : 0
} : aggregate failed
Error: command failed: {
        "errmsg" : "exception: Unrecognized pipeline stage name: '$lookup'",
        "code" : 16436,
        "ok" : 0
} : aggregate failed
    at Error (<anonymous>)
    at doassert (src/mongo/shell/assert.js:11:14)
    at Function.assert.commandWorked (src/mongo/shell/assert.js:254:5)
    at DBCollection.aggregate (src/mongo/shell/collection.js:1278:12)
    at (shell):1:26
2016-04-29T14:13:48.223+0000 E QUERY    Error: command failed: {
        "errmsg" : "exception: Unrecognized pipeline stage name: '$lookup'",
        "code" : 16436,
        "ok" : 0
} : aggregate failed
    at Error (<anonymous>)
    at doassert (src/mongo/shell/assert.js:11:14)
    at Function.assert.commandWorked (src/mongo/shell/assert.js:254:5)
    at DBCollection.aggregate (src/mongo/shell/collection.js:1278:12)
    at (shell):1:26 at src/mongo/shell/assert.js:13

Answer 1

这是一个很好的现实生活问题。为此，我们可以使用聚合框架和“连接”表，然后将结果写入新集合。之后可以重命名/删除源，我们的输出也可以重命名。这是使用 mongo 控制台完成的。

请找到连接 table1 和 table0 的解决方案，并使用它来执行其他连接。

db.table1.aggregate([    
    {
        $lookup:{
            from:"table0",
            localField: "table0_Id", // this is our join source
            foreignField: "id", // this id field in table0 collection
            as: "table0_docs"
            }
    },
    {
        $unwind:"$table0_docs"
        },
    {
        $project:{
            // very important list all fields here
            _id:1,
            data:1,
            oldId:"$table0_Id",
            referenceID:"$table0_docs._id",

            }        
        },
        {
            $out:"newCollectionName"
            }
    ])

AND OUTPUT DOCUMENT

{
    "_id" : ObjectId("57234f5de63d33670e521892"),
    "data" : "22",
    "oldId" : 1,
    "referenceID" : ObjectId("57234f33e63d33670e52188e")
}

欢迎任何评论!