Mongodb $lookup when localField is string and foreignField is in ObjectId formats

Question

Trying to do mongodb aggregate $lookup query for following collections :

Shop collection :

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

Client collection :

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

the query :

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

above query ends up giving an empty patient array :

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

Edit :

and when trying to lookup using an array of ids as local Field :

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

by using :

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

this leads to an Mongo Server error.

Edit 02 :

when trying to lookup for localField which is a nested as follows :

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

by using :

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

this leads to an Mongo Server error too.

Dĵ ΝιΓΞΗΛψΚ Dĵ ΝιΓΞΗΛψΚ · Accepted Answer · 2020-04-10T10:45:01

this should do it:

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

update: array of id strings

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

Mongodb $lookup when localField is string and foreignField is in ObjectId formats

1 Answers