MongoDB查询实现 DBRef field的lookup

在Aggregation Pipeline Stages中，有一个$lookup函数，具体的用法如下

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

而如果localField是 DBRef里面的$id，那么则不能lookup

   $lookup:
     {
       from: "tableA",
       localField: "tableA.$id",
       foreignField: "_id",
       as:"tableA"
     }
}

会返回如下错误

assert: command failed:
{ "ok" : 0, "errmsg" : "FieldPath field names may not start with '$'.", "code" : 16410 }

原因是管道函数中限制了fieldName不能以$开始，所以此处会报错。

When change notification is first stage in aggregation any data manipulation that's done in that pipeline may need to project or check/use the value of "o.$__" field ($set, $unset, etc).

解决办法:在lookup前加入下面管道代码生成一个去掉$的field

{$addFields:{"newfoo":
     {$arrayToObject:{$map:{
          input:{$objectToArray:"$foo"}, 
          in:{
             k:{$cond:[ 
                     {$eq:[{"$substrCP":["$$this.k",0,1]},{$literal:"$"}]},
                     {$substrCP:["$$this.k",1,{$strLenCP:"$$this.k"}]},
                     "$$this.k"
             ]},
             v:"$$this.v"
           }
         }}}
}}

此处的newfoo为新增的field的别名，foo为DBRef的field,其它代码为固定写法，也就是可以复制此处代码修改两个地方，效果如下
before

{
    "foo":{
        "$ref":"foo",
        "$id":"123"
    }
}

after

{
    "foo":{
        "$ref":"foo",
        "$id":"123"
    },
    "newfoo":{
        "ref":"foo",
        "id":"123"
    }
}

那么我们就可以开心的使用lookup了。

参考官方还在OPEN的defectSERVER-14466,也许以后可以用$开头作为fieldName使用，但是暂时的代替方法为上述方法.