CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
cityvoice
cityvoice

Reputation: 2651

How to convert ObjectID to String in $lookup (aggregation)

I have two collections, article and comments, the articleId in comments is a foreign key of _id in article.

db.collection('article').aggregate([
  {
    $lookup: {
      from: "comments",
      localField: "_id",
      foreignField: "articleId",
      as: "comments"
    }
  },
  ...
])

but it doesn't work, because _id in article is an ObjectID and articleId is a string.

Upvotes: 15

Views: 18769

Answers (2)

Ashh
Ashh

Reputation: 46491

You can achieve this using $addFields and $toObjectId aggregations which simply converts string id to mongo objectId

db.collection('article').aggregate([
  { "$lookup": {
    "from": "comments",
    "let": { "article_Id": "$_id" },
    "pipeline": [
      { "$addFields": { "articleId": { "$toObjectId": "$articleId" }}},
      { "$match": { "$expr": { "$eq": [ "$articleId", "$$article_Id" ] } } }
    ],
    "as": "comments"
  }}
])

Or using $toString aggregation

db.collection('article').aggregate([
  { "$addFields": { "article_id": { "$toString": "$_id" }}},
  { "$lookup": {
    "from": "comments",
    "localField": "article_id",
    "foreignField": "articleId",
    "as": "comments"
  }}
])

Upvotes: 27

Biswabhusan Pradhan
Biswabhusan Pradhan

Reputation: 52

If you are using MongoDB 4.0 or above then you can directly use $toString on the _id field:

db.collection('article').aggregate([
  {
    $lookup: {
      from: "comments",
      localField: { $toString : "_id" },
      foreignField: "articleId",
      as: "comments"
    }
  },
  ...
])

But MongoDB 3.6 doesn't support type conversion inside an aggregation pipeline. So $toString and $convert will only work with MongoDB 4.0.

Upvotes: -7

Related Questions