当localField为字符串并且foreignField为ObjectId格式时，Mongodb$lookup

Question

尝试对以下集合执行mongodb聚合$lookup查询：

店铺收藏：

{ 
"_id" : ObjectId("5b618a57759612021aaa2ed"),  
"no" : "23456", 
"date" : ISODate("2012-01-04T16:00:00.000+0000"), 
"clientId" : "5b55cc5c05546200217ae0f3"
}

客户端集合：

{ 
"_id" : ObjectId("5b55cc5c05546200217ae0f3"), 
"title" : "Ms",
"name" : "Jane Marie"
}

查询：

db.getCollection("shop").aggregate([
{ $lookup:
{
from: "client",
localField: "clientId",
foreignField: "_id",
as: "client"
}
}
])

上面的查询最终给出了一个空的患者数组：

{ 
"_id" : ObjectId("5b618a57759672021aaa2ed"),  
"no" : "20190000024274", 
"date" : ISODate("2012-01-04T16:00:00.000+0000"), 
"clientId" : "5b55cc5c05546200217ae0f3",
"client" : []
}

编辑：

以及当尝试使用id数组作为本地字段进行查找时：

transaconsIds: ["5b61d4320550de077143b763", "5b61d4324450de002143b777"]

通过使用：

{
$lookup:
{
from: "transcation",
let: { vid: "transaconsIds" },
pipeline: [
{
$match: {
$expr: {
$eq: ["$_id", { $toObjectId: "$$vid" }]
}
}
}
],
as: "transactions"
}
}

这会导致Mongo Server错误。

编辑02:

当试图查找嵌套的localField时，如下所示：

"transactions" : [
{
"bill" : {
"soldItemIds" : [
"5b55aabf0550770021097ed2"
]
}
}
]

通过使用：

{ $unwind : "$transactions"},
{
$lookup:
{
from: "bill",
let: { did: "$transactions.bill.soldItemIds" },
pipeline: [
{
$match: {
$expr: {
$in: ["$_id", {
$map: {
input: "$$did",
in: { $toObjectId: "$$this" }
}
}
]
}
}
}
],
as: "bills"
}
}

这也会导致Mongo-Server错误。

Answer 1

这应该做到：

db.shop.aggregate([
{
$lookup:
{
from: "client",
let: { pid: "$clientId" },
pipeline: [
{
$match: {
$expr: {
$eq: ["$_id", { $toObjectId: "$$pid" }]
}
}
}
],
as: "client"
}
},
{
$set: {
client: {
$arrayElemAt: ["$client", 0]
}
}
}
])

更新：id字符串数组

db.collection.aggregate(
[
{
$lookup:
{
from: "transactions",
let: { vid: "$transactionIds" },
pipeline: [
{
$match: {
$expr: {
$in: ["$_id", {
$map: {
input: "$$vid",
in: { $toObjectId: "$$this" }
}
}
]
}
}
}
],
as: "transactions"
}
}
])

Answer 2

db.collection("shop").aggregate([
{ $match: { _id: shopId } },
{ $addFields: {
convertedId: {$toObjectId: "$clientId"}
}},
{ $lookup: {
from: "users",
localField: "convertedId",
foreignField: "_id",
as: "result"
}}
]);