MongoDB中的多表关联查询（$lookup）将ObjectID转换为String

$lookup的功能及语法

是将每个输入待处理的文档，经过$lookup 阶段的处理，输出的新文档中会包含一个新生成的数组列（用户可根据需要命名新key的名字 ）。数组列存放的数据是来自 被Join 集合的适配文档，如果没有，集合为空（即 为[ ]）

注意事项

1. 需要3.2及以上版本才支持$lookup
2. 需要4.0及以上版本才支持$convert

基本语法

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

语法说明

语法	说明
from	同一个数据库下等待被Join的集合。
localField	源集合中的match值，如果输入的集合中，某文档没有 localField这个Key（Field），在处理的过程中，会默认为此文档含有 localField：null的键值对。
foreignField	待Join的集合的match值，如果待Join的集合中，文档没有foreignField值，在处理的过程中，会默认为此文档含有 foreignField：null的键值对
as	为输出文档的新增值命名。如果输入的集合中已存在该值，则会覆盖掉

参考案例

1. 主表

主表id为ObjectId类型

db.getCollection('note').find();

查询结果：

{
    "_id" : ObjectId("5f9faba46b299d1336f9d316"),
    "noteCode" : "20201102144804000001",
    "userId" : 93,
    "title" : "标题",
    "content" : "内容"
},
{
    "_id" : ObjectId("5f9fabb06b299d1336f9d31c"),
    "noteCode" : "20201102144816000001",
    "userId" : 93,
    "title" : "标题",
    "content" : "内容"
}

2. 子表

外键noteId为String类型

/* 1 */
{
    "_id" : ObjectId("5f9faba46b299d1336f9d317"),
    "noteId" : "5f9faba46b299d1336f9d316",
    "imgId" : 316,
    "imgUrl" : "https://xxx/selection1577778815396.png",
    "createTime" : ISODate("2020-11-02T14:48:04.356+08:00")
}

/* 2 */
{
    "_id" : ObjectId("5f9faba46b299d1336f9d318"),
    "noteId" : "5f9faba46b299d1336f9d316",
    "imgId" : 3165,
    "imgUrl" : "https://xxx/selection157777881521.png",
    "createTime" : ISODate("2020-11-02T14:48:04.356+08:00")
}

3. 关联查询，将关联ID类型转换为一致(objectId to string)

db.getCollection("note").aggregate(
    [{
        "$project": 
        {
            "id": 
            {
                "$convert": {
                    "input": "$_id",
                    "to": "string"
                }
            },
            "noteCode": 1
        }
    }, {
        "$lookup": 
        {
            "from": "noteImage",
            "localField": "id",
            "foreignField": "noteId",
            "as": "image_docs"
        }
    }]
);

输出结果:

{
    "_id" : ObjectId("5f9faba46b299d1336f9d316"),
    "noteCode" : "20201102144804000001",
    "id" : "5f9faba46b299d1336f9d316",
    "image_docs" : [ 
        {
            "_id" : ObjectId("5f9faba46b299d1336f9d317"),
            "noteId" : "5f9faba46b299d1336f9d316",
            "imgId" : 316,
            "imgUrl" : "https://xxx/selection1577778815396.png",
            "createTime" : ISODate("2020-11-02T14:48:04.356+08:00")
        }, 
        {
            "_id" : ObjectId("5f9faba46b299d1336f9d318"),
            "noteId" : "5f9faba46b299d1336f9d316",
            "imgId" : 3165,
            "imgUrl" : "https://xxx/selection1577778815396.png",
            "createTime" : ISODate("2020-11-02T14:48:04.356+08:00")
        }
    ]
}

4. 关联查询，将关联ID类型转换为一致(string to objectId)

db.getCollection("noteImage").aggregate(
    [{
        "$project": 
        {
            "nid": 
            {
                "$convert": {
                    "input": "$noteId",
                    "to": "objectId"
                }
            },
            "imgId": 1
        }
    }, {
        "$lookup": 
        {
            "from": "note",
            "localField": "nid",
            "foreignField": "_id",
            "as": "noteDocs"
        }
    }]
);

输出结果:

// 1
{
    "_id": ObjectId("5fa9eab6e7e2af281425d0c9"),
    "imgId": 2686,
    "nid": ObjectId("5fa9eab6e7e2af281425d0c8"),
    "noteDocs": [
        {
            "_id": ObjectId("5fa9eab6e7e2af281425d0c8"),
            "noteCode": "9223372036854775807",
            "userId": NumberInt("99"),
            "title": "联调专用",
            "content": "联调数据"           
        }
    ]
}

// 2
{
    "_id": ObjectId("5fa9ee7ae7e2af281425d10a"),
    "imgId": 2872,
    "nid": ObjectId("5fa9ee7ae7e2af281425d109"),
    "noteDocs": [
        {
            "_id": ObjectId("5fa9ee7ae7e2af281425d109"),
            "noteCode": "9223372036854775807",
            "userId": NumberInt("90"),
            "title": "吃饭",
            "content": "吃饭"
        }
    ]
}