CODEWITHSUNDEEP
node.jsmongodbmongoose
JorgeFo
JorgeFo

Reputation: 611

Mongodb $lookup Not working with _id

wend try with this query, return the lookup is empty

db.getCollection('tests').aggregate([
    {$match: {typet:'Req'}},
    {$project: {incharge:1}},
    {$lookup:{
            from: "users",
            localField: "incharge", //this is the _id user from tests
            foreignField: "_id", //this is the _id from users
            as: "user"
    }}
])

return json

  [
    {
        "_id": "57565d2e45bd27b012fc4db9",
        "incharge": "549e0bb67371ecc804ad23ef",
        "user": []
    },
    {
        "_id": "57565d2045bd27b012fc4cbb",
        "incharge": "549e0bb67371ecc804ad21ef",
        "user": []
    },
    {
        "_id": "57565d2245bd27b012fc4cc7",
        "incharge": "549e0bb67371ecc804ad24ef",
        "user": []
    }
]

i try with this post but nothing happend MongoDB aggregation project string to ObjectId and with this MongoDB $lookup with _id as a foreignField in PHP

UPDATE

this is the Document "users"

    {
        "_id" : ObjectId("549e0bb67371ecc804ad24ef"),
        "displayname" : "Jhon S."
    },
    {
        "_id" : ObjectId("549e0bb67371ecc804ad21ef"),
        "displayname" : "George F."
    },
    {
        "_id" : ObjectId("549e0bb67371ecc804ad23ef"),
        "displayname" : "Franc D."
    }

Upvotes: 24

Views: 28380

Answers (10)

David
David

Reputation: 1111

Another reason it may appear to fail even though it's actually working:

Some documents in the foreign collection have been deleted,

therefore the _id fields you are trying to match don't exist.

your local collection has stored them, but just because the following exists in your local collection

customerId: ObjectId('d36257da015df161c577d0b6')

doesn't mean that this exists in the foreign collection that's you're trying to $lookup

_id: ObjectId('d36257da015df161c577d0b6')

This document has been deleted, so its _id no longer exists.

.... but you think that something is wrong with your $lookup aggregation.

Upvotes: 0

Broli
Broli

Reputation: 1

try this one:

{
  from: 'categories',
  let: { cid: { $toObjectId: '$category_id' } },
  pipeline: [
    { $match: { $expr: { $eq: ['$_id', '$$cid'] } } },
  ],
  as: 'category_data'
}

$lookup foreignField is ObjectId

Upvotes: 0

Flash Noob
Flash Noob

Reputation: 500

If you have stored key in string you can use $addFields (aggregation) to convert the key into objectId

{
        "$addFields": {
            incharge: {
                "$toObjectId": "$incharge"
            }
        }
    }

Upvotes: 1

Touhid Rahman
Touhid Rahman

Reputation: 561

Comparing a string with an ObjectId doesn't throw an error, rather sends an empty array in the aggregated output document. So you need to make sure that you have converted the string object id to mongodb's ObjectId:

db.getCollection('tests').aggregate([
    {$match: {typet:'Req'}},
    {$set: {incharge: {$toObjectId: "$incharge"} }}, // keep the whole document structure, but replace `incharge` into ObjectId
    {$lookup:{
            from: "users",
            localField: "incharge", //this is the _id user from tests
            foreignField: "_id", //this is the _id from users
            as: "user"
    }}
])

Upvotes: 17

Mishhub Mohammed
Mishhub Mohammed

Reputation: 183

try changing type of incharge which is string to ObjectId in aggregate function like this

{ 
    $project : {
        incharge : {
            $toObjectId : "$incharge"
        }
    }
}

Upvotes: 3

user3687220
user3687220

Reputation: 61

Your lookup Query is correct. But it is trying to compare a string type (incharge) with ObjectId (_id). Convert the string to ObjectId as shown below. It works for me.

db.getCollection('tests').aggregate([
{$match: {typet:'Req'}},
{$project: {
   incharge:{
     $toObjectId:"$incharge"
   }
},
{$lookup:{
        from: "users",
        localField: "incharge", //this is the _id user from tests
        foreignField: "_id", //this is the _id from users
        as: "user"
}}

Upvotes: 6

havokles
havokles

Reputation: 316

First, assert that the type of the incharge field is mongoose.Schema.Types.ObjectId. If you still get an empty array back it might be because you are using the schema name you declared in NodeJS instead of the collection name used by MongoDB.

Example from a UserSchema file:

const mongoose = require('mongoose')
const Schema = mongoose.Schema

const UserSchema = new Schema({
  name: { 
    type: String, 
    required: true
  },
  incharge: { 
    type: Schema.Types.ObjectId, 
    required: true
  },
})

const User = mongoose.model('User', UserSchema)
module.exports = User

The model above is named User by mongoose but the corresponding collection in mongoDB is named users. The resulting $lookup is written as:

$lookup:{
  from: "users",           // name of mongoDB collection, NOT mongoose model
  localField: "incharge",  // referenced users _id in the tests collection
  foreignField: "_id",     // _id from users
  as: "user"               // output array in returned object
}



https://mongoosejs.com/docs/models.html
https://mongoosejs.com/docs/schematypes.html

Upvotes: 21

Jay Chouhan
Jay Chouhan

Reputation: 115

You just have to use "_id.str" and work will be done.

db.getCollection('tests').aggregate([
{$match: {typet:'Req'}},
{$project: {incharge:1}},
{$lookup:{
        from: "users",
        localField: "incharge", //this is the _id user from tests
        foreignField: "_id.str", //this is the _id from users
        as: "user"
}}

])

Works fine for me.

Upvotes: 9

Parth Vyas
Parth Vyas

Reputation: 507

Your lookup query is perfect, but the problem is you are storing incharge as string into the db, whereas the _id : ObjectId('theID') is an Object and not just string and you cannot compare a string (' ') with an object ({ }). So, the best way is to store the incharge key as an object(mongoose.Schema.ObjectId) and not as string in the schema.

Upvotes: 2

JorgeFo
JorgeFo

Reputation: 611

I finaly found the solution, is a problem with my Schema in mongoose with the ObjectId

I change this 

var Schema = new Schema({
    name: { type: String, required: true},
    incharge: { type: String, required: true},
});

with this

var Schema = new Schema({
    name: { type: String, required: true},
    incharge: { type: mongoose.Schema.ObjectId, required: true},
});

and is working

Upvotes: 36

Related Questions