当 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 数组作为本地 Field 进行查找时:

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

通过使用 :

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

这会导致 Mongo 服务器错误。

编辑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"
                }
        }
    ])