$unwind empty array

Question

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

when you say "solved may be empty" is it present and an empty array or is it absent in case of no problems solved? — Asya Kamsky, Dec 16 '12 at 04:01
also, $first login gets the first value (by order of array) - is that what you want, or do you want $min? If no problems are solved, can login be null or missing also? — Asya Kamsky, Dec 16 '12 at 04:18

chridam · Accepted Answer · 2022-01-18T10:44:08.683

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" }
    } }
])

Wow. Classic _if-true-work-much-better-than-the-unreasonable-default_ flag :/ — Boaz, Oct 23 '22 at 13:43

Asya Kamsky · Answer 2 · 2012-12-16T04:20:06.093

8

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 ] );

edited Dec 16 '12 at 04:20

answered Dec 16 '12 at 04:14

Asya Kamsky

41,784
5
109
133

Hello @AsyaKamsky , I've tried your codes on MongoDB 3.0.3 however $ifNull doesn't work exactly? Also I couldn't anything about this issue on http://docs.mongodb.org/manual/reference/operator/aggregation/ifNull/#exp._S_ifNull . – efkan Aug 18 '15 at 14:46
1

Instead, I've used `'s': { $cond: [{$eq: [{$size: '$solved'}, 0] }, [ { point: 0 } ], '$solved'] }`
and I reported on Jira. – efkan Aug 18 '15 at 15:42
$size did not exist when this answer was written, so I couldn't use it in the solution. Next version (3.2) will have $isArray operator as well. I don't know what you mean by $ifNull not working "exactly" - so can't comment on that. – Asya Kamsky Aug 18 '15 at 16:19
I mean, I thought `$ifNull` would be worked like on documentation `{ $ifNull: [ , ] }` . However it didn't replace the expression like your codes the above. Could you please tell me if there is an alternative operator for me ? (because of there is no longer `$size` operator) – efkan Aug 18 '15 at 16:35
I've solved by using `'s': { $cond: [{$eq: ['$solved', []] }, [ { point: 0 } ], '$solved'] }` . Thanks anyway... – efkan Aug 18 '15 at 16:46
$ifNull only replaces "null" type values (missing or null) it is *not* supposed to replace [ ] value as that is very different from null. – Asya Kamsky Aug 18 '15 at 17:13
Thank you @AsyaKamsky and I want to apologize to you because of that false alarm. After worked 11 hours I thought that I encountered with a bug. And another mistake about `$size` operator regarding with my English grammer. I'm sorry, again for unneccessary traffic on both here and Jira. Have a nice day.. – efkan Aug 20 '15 at 12:59

score 2 · Answer 3 · answered Mar 05 '22 at 12:25

$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,
                },
              },
            ],
          },
        ],
      },
    },
  ]);

$unwind empty array

3 Answers3

Linked

Related