mongodb query using _id object in lookup localField

Question

we are trying to match _id object to mId foreignfield which is not working. looking for mondodb docs. they do not have anything on this. Is it possible with mongodb query or not?

_id as object in document

   "_id" : ObjectId("56ab6663d69d2d1100c074db"),

mId as String in document

"mId" : "56ab6663d69d2d1100c074db",

query as below:

 collection.aggregate([
                {
                  $lookup:
                    {
                      from: "category",
                      localField: "_id",
                      foreignField: "mId",
                      as: "categories"
                    }
               }
            ])

UDPATE

as a summary, mongodb does not support type coercion in $lookup. so to need above working i have to have _id and mId as ObjectId type in documents itself to make it work.

What is `mId` ? If it is an `ObjectId` as well, it should work fine. — BatScream, Mar 18 '16 at 06:27
it is not ObjectId currently. so we need to set foreign keys as ObjectId itself to make this work? — jit, Mar 18 '16 at 06:29
It is not required. The `$lookup` operator would work as long as `localField === foreignField`. Note the `strict` equals. — BatScream, Mar 18 '16 at 06:31
I assume you want to emulate a JOIN with that aggregation. Please be aware that this aggregation iterates through all your documents and does a query on the other collection for each of the documents. While this may work halfway with only a couple of hundred to thousands of documents, you'll wait minutes when your collection grows larger. — Markus W Mahlberg, Mar 18 '16 at 06:33
@BatScream did not get you. so do you mean that _id and mId should be matched only if they are ObjectId type? — jit, Mar 18 '16 at 06:34
@jit - They need not be only `objectIds`. For example, It would also have worked if `_id` was a `number` and `mId` was also a `number` and both had the same value. — BatScream, Mar 18 '16 at 06:36
You will have issues, since this most likely hides a bad data model. Just you can misuse a feature does not make it a good idea. — Markus W Mahlberg, Mar 18 '16 at 06:37
@BatScream so it contradicts to your "It is not required." statement. i need to set mId as ObjectId while saving or any other way to do that? — jit, Mar 18 '16 at 06:37
@jit - I have updated my previous comment. In your example, since _id is an `ObjectId`, and you are using the field to lookup, the compared field also should be an `objectId`, having the same value. — BatScream, Mar 18 '16 at 06:39
@BatScream Thanks. so we do not have any dynamic typecasting thing here in mongodb? — jit, Mar 18 '16 at 06:41
@jit- Welcome. There is no type coercion. Please See [this](https://docs.mongodb.org/manual/reference/bson-types/#comparison-sort-order). — BatScream, Mar 18 '16 at 06:54

Prashant Pokhriyal · Answer 1 · 2019-02-12T10:12:09.883

5

From Mongodb 4.0 onwards, you can use $toString aggregation operator to convert ObjectId to string.

Jira issue : Allow $lookup between ObjectId (_id.str) and string

Now your query should be like below :

collection.aggregate([
  { 
    $addFields: { "_id": { "$toString": "$_id" } }
  },
  {
    $lookup: {
      from: "category",
      localField: "_id",
      foreignField: "mId",
      as: "categories"
    }
  }
])

edited Feb 12 '19 at 10:12

answered Aug 01 '17 at 12:47

Prashant Pokhriyal

3,727
4
28
40

1

This worked great thanks. A useful feature to note, the `$addFields` can also work for the `foreignField` value as well - which is what I needed in my case. – Luke Brown Dec 18 '19 at 18:03
@LukeBrown Can you please elaborate how you did it for the `foreignField`? Since we are running the aggregation on the current collection, it is unclear to me how you could fish for the field to convert to string from the _other_ collection. – Sebastian Kristof Oct 18 '22 at 17:07

score 0 · Answer 2 · answered Oct 07 '16 at 06:32

0

You can join id with mid.Though $lookup does not support coercion,use $project pipeline stage and first convert all the mid to Object Id and then try to $lookup

answered Oct 07 '16 at 06:32

Parshuram Kalvikatte

1,616
4
20
40

and how possibly can we do that conversion from string to object id inside aggregation ?? – Prakash Kumar Dec 08 '16 at 22:03
1

A bit late to the party but adding this project stage to the aggregation should work: { $project: { _id: {$toObjectId: "$_id"} }} – ashe540 Jan 14 '17 at 17:32
1

ashe540, there is no "$toObjectId" operator. – Ivan Gusev Mar 10 '17 at 12:13
There is no $toObjectId but we can convert to objectId Refer this http://stackoverflow.com/questions/7825700/convert-string-to-objectid-in-mongodb – Parshuram Kalvikatte Mar 10 '17 at 12:27

mongodb query using _id object in lookup localField

2 Answers2

Linked