CODEWITHSUNDEEP
mongodbjoinmongodb-queryaggregation-frameworklookup
Kavya Mugali
Kavya Mugali

Reputation: 1118

Mongodb Join on _id field from String to ObjectId

I have two collections

  1. User

    {
   "_id" : ObjectId("584aac38686860d502929b8b"),
   "name" : "John"
}

  2. Role

    {
   "_id" : ObjectId("584aaca6686860d502929b8d"),
   "role" : "Admin",
   "userId" : "584aac38686860d502929b8b"  
}

I want to join these collection based on the userId (in role collection) - _id ( in user collection).

I tried the below query:

db.role.aggregate({
  "$lookup": {
    "from": "user",
    "localField": "userId",
    "foreignField": "_id",
    "as": "output"
  }
})

This gives me expected results as long as i store userId as a ObjectId. When my userId is a string there are no results. Ps: I tried

foreignField: '_id'.valueOf()

and

foreignField: '_id'.toString()

. But no luck to match/join based on a ObjectId-string fields.

Any help will be appreciated.

Upvotes: 76

Views: 87126

Answers (4)

Alex Rublev
Alex Rublev

Reputation: 303

For example we have two collections:

  • Authors like {'_id': ObjectId(), name: 'John Smith'}
  • Messages like {'_id': ObjectId(), text: 'Message Text', authorId: 'stringId'}
  • And we need to get messages with author names and other data

Then:

db.messages.aggregate([
  {
    $addFields: {
      '$authorObjectId': {$toObjectId: $authorId}  
    }
  },
  {
    $lookup: {
      from: 'authors',
      localField: '$authorObjectId',
      foreignField: '_id',
      as: 'author'
    }
  }
 ])

Explained:

Our aggregation pipeline has two steps:

  • First: We add additional field to messages which contains converted string authorId to ObjectId()
  • Second: We use this field as localField (in messages) to be compared with foreignField '_id' (in authors)

Upvotes: 2

Gary Wild
Gary Wild

Reputation: 721

I think the previous answer has an error on the $toObjectId case. The let statement applies to the db collection on which the function aggregate is called (i.e 'role') and not on the collection pointed by "from" (i.e 'user').

db.role.aggregate([
  { "$lookup": {
    "let": { "userObjId": { "$toObjectId": "$userId" } },
    "from": "user",
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$_id", "$$userObjId" ] } } }
    ],
    "as": "userDetails"
  }}
])

Or

db.role.aggregate([
  { "$project": { "userObjId": { "$toObjectId": "$userId" } } },
  { "$lookup": {
    "localField": "userObjId",
    "from": "user",
    "foreignField": "$_id",
    "as": "userDetails"
  }}
])

And

db.user.aggregate([
  { "$project": { "userStrId": { "$toString": "$_id" }}},
  { "$lookup": {
    "localField": "userStrId",
    "from": "role",
    "foreignField": "userId",
    "as": "roleDetails"
  }}
])

Upvotes: 59

felix
felix

Reputation: 9295

This is not possible as of MongoDB 3.4. This feature has already been requested, but hasn't been implemented yet. Here are the corresponding tickets:

For now you'll have to store userId as ObjectId

EDIT

The previous tickets were fixed in MongoDB 4.0. You can now achieve this with the folowing query: 

db.user.aggregate([
  {
    "$project": {
      "_id": {
        "$toString": "$_id"
      }
    }
  },
  {
    "$lookup": {
      "from": "role",
      "localField": "_id",
      "foreignField": "userId",
      "as": "role"
    }
  }
])

result: 

[
  {
    "_id": "584aac38686860d502929b8b",
    "role": [
      {
        "_id": ObjectId("584aaca6686860d502929b8d"),
        "role": "Admin",
        "userId": "584aac38686860d502929b8b"
      }
    ]
  }
]

try it online: mongoplayground.net/p/JoLPVIb1OLS

Upvotes: 50

Ashh
Ashh

Reputation: 46491

You can use $toObjectId aggregation from mongodb 4.0 which converts String id to ObjectId

db.role.aggregate([
  { "$lookup": {
    "from": "user",
    "let": { "userId": "$_id" },
    "pipeline": [
      { "$addFields": { "userId": { "$toObjectId": "$userId" }}},
      { "$match": { "$expr": { "$eq": [ "$userId", "$$userId" ] } } }
    ],
    "as": "output"
  }}
])

Or you can use $toString aggregation from mongodb 4.0 which converts ObjectId to String 

db.role.aggregate([
  { "$addFields": { "userId": { "$toString": "$_id" }}},
  { "$lookup": {
    "from": "user",
    "localField": "userId",
    "foreignField": "userId",
    "as": "output"
  }}
])

Upvotes: 85

Related Questions