list documents that one field is blank or doesnt exists

14 views
Skip to first unread message

Alexandre Caruso

unread,
Mar 11, 2021, 4:20:18 PMMar 11
to seektable-users
I have a collection of users and some of them didn't fill in profile information (it was not required) 
now I need to list or count which users don't have this field.

on MongoDb I use :

db.getCollection('usuario').find({perfis:{"$nin":[null,""]}}).count()
db.getCollection('usuario').find({perfis:{$exists:true}}).count()

Keep in mind I'm no expert and still learning. So pls look over my level.
I'll appreciate 

VF [SeekTable founder]

unread,
Mar 12, 2021, 2:05:30 AMMar 12
to seektable-users
To display counts you need to have a dimension like "Has Profile" with 2 possible values (true/false or yes/no) that are calculated as MongoDb projection.
How to configure this kind of dimension:
  1. Go to the cube's configuration form
  2. Add a new dimension with Type=Field and Name=has_profile (optionally you can specify user-friendly caption by setting "Label")
  3. Add one value to "Parameters" with field's projection JSON definition:
    {$cond: [
      { $or : [ 
          { $eq : [ {$ifNull: ["$prefis", ""] }, ""] },
          { $eq: ["$prefis", undefined] }
       ] },
      "No", "Yes"]}


    where
      { $eq : [ {$ifNull: ["$prefis", ""] }, ""] }    is an projection equivalent of find {perfis:{"$nin":[null,""]}}
     { $eq: ["$prefis", undefined] }
       is an projection equivalent of find   {perfis:{$exists:true}}
  4. Save the form; now you can create a pivot table report with "has_profile" dimension and "Count" measure to show number of users with and without profile.
    Click on the cell with value to drill-down -- here you can choose "Show rows" and display users filtered by the "has_profile" field.

Useful links:
Reply all
Reply to author
Forward
0 new messages