Mongo aggregation lookup

Question

I am trying to attempt the following

From a log, find all prizes the user entered return the order numbers and the title

I am using the mongo aggregation framework.

I match by userId
I group the prizes by the same product and then push order numbers
I want to do a look up to the prizes collection to find the title of the prizes.

The look up is returning no elements

Here is the aggregation code

db.pointslogs.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $match: {
                "user": ObjectId("5aacff47c67f99103bcbf693")
            }
        },

        // Stage 2
        {
            $group: {
                _id: "$productPurchased",
                orderNumber: { $push: "$orderNumber" }
            }
        },

        // Stage 3
        {
            $unwind: {
                path : "$orderNumber",

            }
        },

        // Stage 4
        {
            $lookup: {
                "from" : "prizes",
                "localField" : "_id",
                "foreignField" : "_id",
                "as" : "title"
            }
        },

    ]

    // Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/

);

Here are the models (points log below)

var mongoose = require('mongoose');
var Schema =  mongoose.Schema;

var schema = new Schema({
    orderNumber: {type: String, required: true},
    productPurchased: {type: String, require: true},
    answer: {type: String},
    user: [{type: Schema.Types.ObjectId, ref: 'User'}]
});

module.exports =  mongoose.model('PointsLog', schema);

Prizes model below

var mongoose = require('mongoose');
var Schema =  mongoose.Schema;

var schema = new Schema({
    title: {type: String, required: true},
    content: {type: String, required: true},
    orderNumber: {type: String, required: true},
    imageUrl: {type: String, required: true},
    stockQty: {type: Number, required: true},
    question: {type: String, required: true},
    answers: [{type: String, required: true}],
    image: {type: String, required: true},
    cost: {type: Number, required: true },
    //entries: {type: Int},
    //user: {type: Schema.Types.ObjectId, ref: 'User'}
    user: [{type: Schema.Types.ObjectId, ref: 'User'}]

});

module.exports =  mongoose.model('Prize', schema);

Not sure why lookup is returning nothing

`productPurchased` is a "string" as defined in your schema. What are you expecting that to match from the `"prizes"` collection? If you think it "looks like" an `ObjectId` which the target collection has as it's `_id` field, then look again. Strings and `Objectid` values are different, no matter how you "pretty print" them. Show two documents and point to the things you expect to match if you still do not understand. — Neil Lunn, May 01 '18 at 10:58
The productPurchased is the _id of a prize but it is stored as string , so what I think you're saying is in the aggregation framework you cannot match a string to an objectId, so it's best when writing the logs to store as objectId ant not string? — user2389087, May 01 '18 at 11:31
See [Update MongoDB field using value of another field](https://stackoverflow.com/questions/3974985/update-mongodb-field-using-value-of-another-field). There is no other way to "update" the field in the collection without looping all documents in the collection, casting to `ObjectId` via a function and writing the data back. — Neil Lunn, May 01 '18 at 11:33

score 0 · Answer 1 · answered Aug 12 '18 at 06:39

 1)You can use the upgraded lookup


        Below is an Example->

        db.pointslogs.aggregate([
 // Stage 1
        {
            $match: {
                "user": ObjectId("5aacff47c67f99103bcbf693")
            }
        },

        // Stage 2
        {
            $group: {
                _id: "$productPurchased",
                orderNumber: { $push: "$orderNumber" }
            }
        },

        // Stage 3
        {
            $unwind: {
                path : "$orderNumber",

            }
        },

        // Stage 4
           {
              $lookup:
                 {
                   from: "prizes",
                   let: { oNumber: "$orderNumber" },
                   pipeline: [
                      { $match:{ $expr: { $eq: [ "$orderNumber",  "$$oNumber" ] }                  }
                         }
                      },
                      { $project: { 
        title: 1,
        content: 1,
        orderNumber: 1,
        imageUrl: 1,
        stockQty: 1,
        question: 1,
        answers: 1,
        image: 1,
        cost: 1,
        user: 1
    } }
                   ],
                   as: "orderNumber"
                 }
            }
        ])

Mongo aggregation lookup

1 Answers1