Limiting fields on $lookup mongo

Question

I have a Mongo object with the below schema.

{
    "_id" : "59fffda313d02500116e83bf::5a059c67f3ff3b001105c509",
    "quizzes" : [ 
        {
            "topics" : [ 
                ObjectId("5a05a1e1fc698f00118470e2")
            ],
            "isCorrect" : true,
            "status" : 2,
            "isUsed" : true,
            "askBack" : false,
            "isRestricted" : false,
            "rejected" : false,
            "createdAt" : ISODate("2017-11-10T12:56:01.273Z"),
            "updatedAt" : ISODate("2017-11-10T12:56:02.808Z"),
            "answerRead" : false,
            "participantAnswer" : "5a054210d74fe90011d5f5e2",
            "correctAnswer" : "5a054210d74fe90011d5f5e2",
            "question" : ObjectId("5a054210d74fe90011d5f5e0"),
            "participant" : ObjectId("59fffda313d02500116e83bf"),
            "author" : ObjectId("5a059c67f3ff3b001105c509"),
            "_id" : ObjectId("5a05a1e1fc698f00118470e1")
        }, 
        {
            "topics" : [ 
                ObjectId("5a054210d74fe90011d5f5e1")
            ],
            "isCorrect" : false,
            "status" : 2,
            "isUsed" : true,
            "askBack" : true,
            "isRestricted" : false,
            "rejected" : false,
            "createdAt" : ISODate("2017-11-10T12:57:33.910Z"),
            "updatedAt" : ISODate("2017-11-10T12:57:33.910Z"),
            "answerRead" : true,
            "correctAnswer" : "5a054210d74fe90011d5f5e2",
            "question" : ObjectId("5a054210d74fe90011d5f5e0"),
            "participant" : ObjectId("5a059c67f3ff3b001105c509"),
            "author" : ObjectId("59fffda313d02500116e83bf"),
            "_id" : ObjectId("5a05a23dfc698f00118470f5")
        }
    ],
    "participants" : [ 
        ObjectId("5a059c67f3ff3b001105c509"), 
        ObjectId("59fffda313d02500116e83bf")
    ],
    "__v" : 0,
    "skippedQuestionIds" : []
}

The 'author' and 'participant' fields are foreign key references to another collection with the below schema.

{
"_id" : ObjectId("5739bcdd4fb7d8030050bc04"),
"password" : "$2a$06$4Rmiya6Vh9aENXxKoD56UOIG3ZiGNzH.Ed5lgY8aiJ4OidQPAf6wq",
"email" : "m@m.com",
"username" : "mm",
"birthday" : ISODate("2016-05-03T18:30:00.000Z"),
"lastName" : "m",
"firstName" : "m",
"devices" : [],
"role" : "user",
"updatedAt" : ISODate("2017-08-01T13:26:43.510Z"),
"createdAt" : ISODate("2016-05-16T12:28:13.063Z"),
"lastUpdated" : ISODate("2017-08-02T07:39:02.960Z"),
"active" : false,
"points" : 0,
"topicLocked" : [ 
    "flirty", 
    "dirty", 
    "mahesh_test"
],
"hashfriends" : [],
"blockedList" : [],
"friends" : [ 
    {
        "user" : ObjectId("586b6fd12631000004812a68"),
        "_id" : ObjectId("58e60399ca9a180004072089"),
        "topicExhausted" : [],
        "scoreInPercentage" : 0,
        "totalQuizAnswered" : 1,
        "isCrew" : false,
        "isFriendo" : false,
        "scoreRank" : "You’re tied 1 to 1",
        "score" : 1,
        "lastPlayed" : ISODate("2017-04-06T09:37:48.843Z")
    }
],
"meta" : {
    "totalInvites" : -3,
    "totalFriends" : 1
},
"isDeleted" : false,
"__v" : 3,
"androidDevices" : [],
"iosDevices" : [],
"isAgeRestrictedCategories" : [ 
    ObjectId("571727dde30aef0300a95725")
],
"usernameAsEntered" : "mm",
"platform" : "ios",
"celebParticipants" : [],
"lastActivity" : ISODate("2017-04-06T09:30:34.054Z"),
"lastNudged" : ISODate("2016-04-25T06:46:38.267Z")
}

I need to run an aggregate on the first collection and limit the number of objects in the quizzes subdocument, and also sort it on the updatedAt timestamp.

I also want to populate author and participant inside each element in quizzes array, from the users collection. However, I'm having a hard time retrieving the fields I need, i.e only username, firstName, lastName, _id, meta and active.

I managed upto this point.

db.getCollection('usergames').aggregate([{$match: {_id: "5a059c56f3ff3b001105c508::5a059c67f3ff3b001105c509"}},
{$unwind: '$quizzes'},
{$lookup: {
    "from":'users',
    "localField":'quizzes.author',
    'foreignField': '_id',
    "as": 'quizzes.author'
}},
{$unwind: '$quizzes.author'},
{$sort: {'quizzes.updatedAt': -1}},
{$group: {_id: '$_id', quizzes: {$push: '$quizzes'}}},
{$project: {
  _id: 1,
  'quizzes': {
            $slice:['$quizzes', 0, 3]
  }}
}
])

However, this is the response I desire.

{
"_id" : "59fffda313d02500116e83bf::5a059c67f3ff3b001105c509",
"quizzes" : [ 
    {
        "topics" : [ 
            ObjectId("5a054210d74fe90011d5f5e1")
        ],
        "isCorrect" : false,
        "status" : 2,
        "isUsed" : true,
        "askBack" : true,
        "isRestricted" : false,
        "rejected" : false,
        "createdAt" : ISODate("2017-11-10T12:57:33.910Z"),
        "updatedAt" : ISODate("2017-11-10T12:57:33.910Z"),
        "answerRead" : true,
        "correctAnswer" : "5a054210d74fe90011d5f5e2",
        "question" : ObjectId("5a054210d74fe90011d5f5e0"),
        "author" :{
                    "_id": "59fffda313d02500116e83bf",
                    "username": "f2",
                    "lastName": "2",
                    "firstName": "f",
                    "active": false,
                    "meta": {
                         "totalInvites": 0,
                         "totalFriends": 1
                   }
          },
        "participant": {
                  "_id": "5a059c67f3ff3b001105c509",
                  "username": "chandan",
                  "lastName": "kumar",
                  "firstName": "chandan",
                  "active": false,
                  "meta": {
                     "totalInvites": 0,
                     "totalFriends": 16
                  }

     "_id" : ObjectId("5a05a23dfc698f00118470f5")
    }
],
"participants" : [ 
    ObjectId("5a059c67f3ff3b001105c509"), 
    ObjectId("59fffda313d02500116e83bf")
],
"__v" : 0,
"skippedQuestionIds" : []
}

Can the lookup be done after the initial projection, and how do I limit fields. Any help will be appreciated. Thanks

Answer 1

I won't familiarize myself enough with your schema to work out the entire pipeline, but If I could generalize a bit:

Yes, the $lookup can be done after a $project operation. You can have multiple $project operations (indeed multiple of any operation) in an aggregation.

A $lookup operation could be followed by a $project operation to normalize your results in the following fashion:

{$lookup: {
    "from":'users',
    "localField":'quizzes.author',
    'foreignField': '_id',
    "as": 'authors'
}},
{$project: {
    'author._id': { $arrayElemAt: ['$authors._id', 0] },
    'author.username': { $arrayElemAt: ['$authors.username', 0] },
    // other fields can be projected as needed
}},

Since $lookup returns results in an array, the above example uses $arrayElemAt to get the first element and project the desired properties one by one.