CODEWITHSUNDEEP
mongodbaggregation-framework
Karel Horak
Karel Horak

Reputation: 1072

$unwind empty array

I have a collection of users where each document has following structure:

{
  "_id": "<id>",
  "login": "xxx",
  "solved": [
    {
      "problem": "<problemID>",
      "points": 10
    },
    ...
  ]
}

The field solved may be empty or contain arbitrary many subdocuments. My goal is to get a list of users together with the total score (sum of points) where users that haven't solved any problem yet will be assigned total score of 0. Is this possible to do this with a single query (ideally using aggregation framework)?

I was trying to use following query in aggregation framework:

{ "$group": {
  "_id": "$_id",
  "login": { "$first": "$login" },
  "solved": { "$addToSet": { "points": 0 } }
} }
{ "$unwind": "$solved" }
{ "$group": {
  "_id": "$_id",
  "login": { "$first": "$login" },
  "solved": { "$sum": "$solved.points" }
} }

However I am getting following error:

exception: The top-level _id field is the only field currently supported for exclusion

Thank you in advance

Upvotes: 57

Views: 39926

Answers (3)

Rafiq
Rafiq

Reputation: 11545

$lookup then $unwind inside look up array and that could be empty


  let posts = await Post.aggregate<ActivityDoc>([
    {
      $match: {
        _id: new mongoose.Types.ObjectId(req.params.id),
      },
    },
    {
      $lookup: {
        from: 'users',
        localField: 'user',
        foreignField: '_id',
        as: 'user',
      },
    },
    {
      $unwind: '$user',
    },
    {
      $unwind: {
        path: '$user.follower',
        preserveNullAndEmptyArrays: true,
      },
    },
    {
      $match: {
        $or: [
          {
            $and: [
              {
                'privacy.mode': {
                  $eq: PrivacyMode.EveryOne,
                },
              },
            ],
          },
          {
            $and: [
              {
                'privacy.mode': {
                  $eq: PrivacyMode.MyCircle,
                },
              },
              {
                'user.follower.id': {
                  $eq: req.currentUser?.id,
                },
              },
            ],
          },
        ],
      },
    },
  ]);

Upvotes: 2

chridam
chridam

Reputation: 103455

With MongoDB 3.2 version and newer, the $unwind operator now has some options where in particular the preserveNullAndEmptyArrays option will solve this.

If this option is set to true and if the path is null, missing, or an empty array, $unwind outputs the document. If false, $unwind does not output a document if the path is null, missing, or an empty array. In your case, set it to true:

db.collection.aggregate([
    { "$unwind": {
        "path": "$solved",
        "preserveNullAndEmptyArrays": true
    } },
    { "$group": {
        "_id": "$_id",
        "login": { "$first": "$login" },
        "solved": { "$sum": "$solved.points" }
    } }
])

Upvotes: 159

Asya Kamsky
Asya Kamsky

Reputation: 42352

Here is the solution - it assumes that the field "solved" is either absent, is equal to null or has an array of problems and scores solved. The case it does not handle is "solved" being an empty array - although that would be a simple additional adjustment you could add.

project = {$project : {
        "s" : {
            "$ifNull" : [
                "$solved",
                [
                    {
                        "points" : 0
                    }
                ]
            ]
        },
        "login" : 1
    }
};
unwind={$unwind:"$s"};
group= { "$group" : {
        "_id" : "$_id",
        "login" : {
            "$first" : "$login"
        },
        "score" : {
            "$sum" : "$s.points"
        }
    }
}

db.students.aggregate( [ project, unwind, group ] );

Upvotes: 8

Related Questions