[SERVER-22781] Allow $lookup between ObjectId (_id.str) and string Created: 22/Feb/16  Updated: 06/Apr/23  Resolved: 02/Mar/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.2.1
Fix Version/s: None

Type: New Feature Priority: Minor - P4
Reporter: Jose Antonio Illescas Olmo Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 25
Labels: eng-m, expression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-33171 Add number and objectID parsing conve... Closed
Related
related to SERVER-24947 Need a type conversion mechanism for ... Closed
Assigned Teams:
Query
Participants:

 Description   

this feature allows lookup using _id.str

company: {_id: { $oid: "..."}, countryId: "566eb3c704c7b31facbb0007"}
country: { _id: { $oid: "566eb3c704c7b31facbb0007", name:"..."} 
 
{ $lookup: {
       from: "country",
       localField: "countryId",    <= it's a string field
       foreignField: "_id.str",    <= using string representation of id
       as: "country"
}}



 Comments   
Comment by David Storch [ 29/May/18 ]

Hi prashant.pokhriyal,

This feature was implemented in SERVER-33171, which was first released in development version 3.7.3. It will be available for production use as part of 4.0 GA.

Best,
Dave

Comment by Prashant Pokhriyal [ 28/May/18 ]

May I know in which version this fix will reflect?

Comment by Asya Kamsky [ 02/Mar/18 ]

SERVER-33171 implemented ability to convert between strings and ObjectId. This is currently in the development release and will be available in the next stable release as new expression $convert in aggregation.

Sample syntax:

db.coll.aggregate({$project:{str:{$toString:"$_id"}}})
{ "_id" : ObjectId("5a999cc461d36489a27f2563"), "str" : "5a999cc461d36489a27f2563" }
{ "_id" : "5a999cc461d36489a27f2563", "str" : "5a999cc461d36489a27f2563" }
{ "_id" : "abc", "str" : "abc" }
db.coll.aggregate({$project:{oid:{$convert:{
            input:"$_id", 
            to:"objectId", 
            onError:"Cannot $convert to objectId"
}}}})
{ "_id" :  ObjectId("5a999cc461d36489a27f2563"), "oid" :  ObjectId("5a999cc461d36489a27f2563") }
{ "_id" : "5a999cc461d36489a27f2563", "oid" :  ObjectId("5a999cc461d36489a27f2563") }
{ "_id" : "abc", "oid" :  "Cannot $convert to objectId" }

$convert is the full syntax with "onError" and "onNull" options, $to<Type are helpers which return null on null-ish input and error out on error.

Comment by srivani kambhampati [ 27/Feb/18 ]

It would be good if this is implemented.

Comment by Chema Roldán [ 22/Feb/18 ]

Hi, any news about it? Thanks.

Comment by Ashraf Kamal [ 13/Feb/18 ]

Even I am facing same issue.. Please implement this feature.

Comment by Luis Aldonza [X] [ 21/Dec/17 ]

+1

Comment by Nathan E Hoover [ 09/Dec/17 ]

Wow, I REALLY need this for a project I'm working on now. Changing the data to store the ObjectId is just not possible. Please implement!

Comment by Dan Cumings [ 01/Dec/17 ]

+1

Comment by Cade Embery [ 24/Nov/17 ]

Yeah, surely it can be added something like $oid or project it or something, it's really painful when you have the ID but can't use it in a lookup

I hang out for every new release just to see if this feature is available

Comment by Alfonso Lentini [X] [ 24/Nov/17 ]

+1

Comment by Ygor Lemos [ 23/Nov/17 ]

+1

Comment by Hasan BUDAK [X] [ 23/Nov/17 ]

Any news with this issue? We really need this feature.

Comment by David [X] [ 15/Nov/17 ]

There is no $toObjectId operator in MongoDB. Try searching the documentation and you'll find nothing! So there simply is no way to achieve your goal without changing your data.

Comment by Cade Embery [ 14/Feb/17 ]

It would be so helpful to be able to find a solution for this, the String/ObjectId issue is the biggest pain of using mongodb

Comment by Rafael da Silva Ferreira [X] [ 30/Jan/17 ]

Any news?

Comment by Asya Kamsky [ 01/Sep/16 ]

I believe it will be better to resolve this via proper conversion operator between ObjectId and string as requested in SERVER-24947

Comment by Zhenghui Lee [ 01/Sep/16 ]

I REALLY need this feature, as it can much simplify our programming effort when we only have a string as intermediate aggregate result, but want to match it to a ObjectId in $lookup. Without this, we have to do this by iterating all of items to do the "join" work one by one..

Comment by Patrick Nijkamp [ 09/Aug/16 ]

I really need this as well. It's a logical feature to have to be able to agregate data ID's to strings as well.

Wouldn't it make more sense to input just the string instead of converting it? Comparing that string to the 'id' parameter inside the _id object? I think that would save some resources when doing thousands of comparisons.

Comment by mustapha [ 09/Aug/16 ]

I need this feature.
It is strange "xxx" not equal to "ObjectId('xxxx')".

Comment by Charlie Swanson [ 24/Feb/16 ]

Ah, okay. Yea I think that would be possible. I think we wanted to keep $lookup as simple as possible during the design process, but I'm not sure there's a good reason you shouldn't be allowed to put arbitrary expressions as the 'localField' (in which case, its name would probably be better as 'localValue'). It would be much more complicated to allow doing so for 'foreignField', since we would have no way of expressing it as a query, so would have to do a simple query that is under-selective, then compute the value and filter in the $lookup code.

Comment by Jose Antonio Illescas Olmo [ 24/Feb/16 ]

Its possible next syntax sugar ?

db.company.aggregate([
  { $lookup: {
    from: "country",
    localField: {$toObjectId: "$countryId"}
    foreignField: "_id",
    as: "country"
  }}
])

Thanks

Comment by Charlie Swanson [ 24/Feb/16 ]

Ok thanks.

I'm not sure if either conversion is faster, but I went with converting strings to ObjectIds because you need to query against ObjectIds in your example. $lookup just uses a simple query to compute the matching documents, and there is currently no way to query based on a transformation of your data using a find. So the fix I proposed would be much simpler to implement, and would fit better with the current systems.

Comment by Jose Antonio Illescas Olmo [ 24/Feb/16 ]

Yes, is reasonable.

Only a question about performance what is more slow: convert strings to objectIds or convert objectIds to Strings?

Comment by Charlie Swanson [ 23/Feb/16 ]

I think this use case could be solved by adding an expression to convert a string into an ObjectId. Using the example from the description, you could instead do the following:

db.company.aggregate([
  { $project: {
    countryId: {$toObjectId: "$countryId"}
  }}
  { $lookup: {
    from: "country",
    localField: "countryId"
    foreignField: "_id",
    as: "country"
  }}
])

Does that sound like a reasonable approach?

Generated at Thu Feb 08 04:01:24 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.