mongodb $lookup with foreignfield _id

Question

I want to fetch the result using $lookup having local fields as the array of _id as a string. But its not happening. I also tried by using _id.str but still no results.

db.posts.aggregate([

{
    $addFields: {
        TagCount: { $size: "$tags" }
    }
},
{
    $lookup:
    {
        from: "tags",
        localField: "tags",
        foreignField: "_id",
        as: "tagList"
    }
},
]));

The schema for Post Collection

const postSchema = new mongoose.Schema({
  title: {
    type: String,
    required: true,
    minlength: 10,
    set: v => v.replace(/\s\s+/gi, ' '),
    get: v => { console.log(v); return v.replace(/\s\s+/gi, ' '); }
  },
  content: {
    type: String,
    required: true
  },
  headerImage: {
    type: String
  },
  tags: [{
    type: mongoose.Schema.Types.ObjectId,
    required: true,
    validate: {
    validator: function (v) {
       return v.length > 0;
    }
  },
  message: 'There must be one tag',
  ref: 'tags'
 }],
  creationDate: { type: Date, default: Date.now }
 });

Here is the tag Schema Collection

 const tagSchema = new mongoose.Schema({
   name: {
    type: String,
    required: true,
    minlength: 3,
    set: value => value.replace(/\s\s+/gi, ' '),
    get: value => value.replace(/\s\s+/gi, ' ')
  },
  description: {
    type: String
  },
  creationDate: {
    type: Date,
    default: Date.now
 }  
 }, {
 collection: 'tags'
});

Also, I tried other StackOverflow questions but I didn't find any solutions.

Did you intend for the tags field in posts to be an array of DBRef? — Joe
@turivishal It will work if you don't use custom object Id. Try to replace it with an auto-generated object Id. What I did mistake is I have to insert ObjectId() in array instead of just string mongoplayground.net/p/bVGZ9dCL4Gi. — Jason

Joe Joe · Accepted Answer · 2020-09-13T20:55:41

Including the ref: 'tags' in the schema definition for the tags array makes mongoose store the values as DBRef, not ObjectID.

This is useful if you intend to use the populate function to retrieve the tag documents on the client side.

The field names in an aggregation are $id, $ref, and $db, which are not valid field names when using dotted notation, like you need to do in the aggregation pipeline.

You could convert each object to an array, which would make it look something like [ {"k":"$id", v:ObjectId("...")}, {"k":"$ref","v":"tags"}, {"k":"$db","v":"dbname"}]

Then filter for the $id key, retain the corresponding value, and use that array as the local field.

db.posts.aggregate([
  {$addFields: {
      TagCount: {$size: "$tags"},
      tag_ids: {
        $map: {
          input: "$tags",
          as: "dbref",
          in: {
            $reduce: {
              input: {$objectToArray: "$$dbref"},
              initialValue: [],
              in: {
                $cond: {
                  if: {$eq: ["$$this.k",{$literal: "$id"}]},
                  then: "$$this.v",
                  else: "$$value"
                }
              }
            }
          }
        }
      }
  }},
  {
    $lookup: {
      from: "tags",
      as: "tag_list",
      localField: "tag_ids",
      foreignField: "_id"
    }
  }
])

mongodb $lookup with foreignfield _id

2 Answers