MongoDb exclude null in aggregation $project

Question

I have a data collection in MongoDb with the shape:

[{ "_id": "1",
  "Sq1" : 5, 
  "Sq1comment" : "In general you aaaaaa.", 
  "Sq2" : 8, 
  "S2comment" : null, 
  "Sq3" : 5, 
  "Sq3comment" : "A person bbbbb."
 },
 { "_id": "2",
   "Sq1" : 4, 
   "Sq1comment" : "In general you cc.", 
   "Sq2" : 8, 
   "S2comment" : "A story ff", 
   "Sq3" : 5, 
   "Sq3comment" : null
 }
]

I would like to extract the 'comment' fields, but only reflect those which are not null in the result.

I can extract the fields (Sq1comment; Sq2comment, Sq3comment) one-by-one with a query

db.collection.find({ "Sq1comment": { $not: { $type: 10 } })

for which the output is:

[{ "_id": "1",
  "Sq1comment" : "In general you aaaaaa.", 
  "Sq3comment" : "A person bbbbb."
 }]

or if I do an aggregation $project the 'comment' fields all the nulls are there:

db.collection.aggregate([{$project: 
   { "Sq1comment": "$Sq1comment",
     "Sq2comment": "$Sq2comment",
     "Sq3comment": "$Sq3comment"
   } }])

for which the output is:

[{ "_id": "1",
  "Sq1comment" : "In general you aaaaaa.", 
  "S2comment" : null, 
  "Sq3comment" : "A person bbbbb."
 },
 { "_id": "2",
   "Sq1comment" : "In general you cc.", 
   "S2comment" : "A story ff", 
   "Sq3comment" : null
 }
]

I want a project the data to show all comment fields, but only the entries which are not null. Thus exclude the null fields is the aggregation.

score 0 · Answer 1 · edited Jul 16 '19 at 10:28

Maybe you can try $unwind, then $group to get the result you want.

Like this:

{
  $unwind:
    {
      path: <field path>,
      includeArrayIndex: <string>,
      preserveNullAndEmptyArrays: <boolean>
    }
}

preserveNullAndEmptyArrays will help exclude null ones.

MongoDb exclude null in aggregation $project

1 Answers1