26382

MongoDB Query - limit fields where name matches pattern

Question:

I've read everything I can find about Projection in MongoDB. I'm hoping this is simple and I just missed it due to the overwhelming flexibility of Mongo queries.

In our MySql database, we've adopted a business practice of having "hidden" fields be prefixed with an underscore. Our application knows how to hide these fields.

Moving some data to mongo, I need to retrieve the documents, <em>with ALL underscore prefixed fields omitted</em>. Of course this should be done in the query rather than document manipulation after retrieval.

All the operators like $regex, $in, $all seem to apply to <em>values</em>. I need to build a projection that ignores an unknown number of fields based on their name. Something like:

db.coll.find({}, {"_*": 0})

Of course that doesn't work, but explains the idea.

I should note: this is necessary because the documents are editable by our application users, so I have no idea what the schema might look like. I do know our "internal" fields are prefixed with an _, and those need to be protected by omission from the editor.

Hope it's easy...

Answer1:

You can have a separate field as hidden_fields or something. See the following schema.

{_id: 'myid1', hidden_fields: {"_foo": "bar", "_foo2": "bar2"}, key1: value1 ...}

Now on the basis of above schema just do,

db.collection.find({ ... }, {hidden_fields: 1})

This will display hidden fields. Also you can have indexes on fields within sub documents so no loss in terms of performance as well.

Answer2:

There is no functionality for this for good reason. It would be a nightmare to implement this kind of functionality and it would not scale nor would it be very fast.

The best way to do this currently is to set up a key-value store like:

{ fields: [ {k: "_ghhg", v: 5}, {k: "ghg", v: 6} ] }

You would then $regex on the k field to understand which key names (fields) have underscore in them.

As a piece of advice I would highly recommend prefixed $regexs since they are way more effective at using the indexes you create, i.e. for the query you show: ^_*.

<blockquote>

Moving some data to mongo, I need to retrieve the documents, with ALL underscore prefixed fields omitted. Of course this should be done in the query rather than document manipulation after retrieval.

</blockquote>

I would personally do this client side, it will be 100x faster than database side.

Answer3:

As was mentioned by @Sammaye, MongoDB doesn't support this type of query in a natural/efficient way.

However, to optimize performance if you don't always need the internal data, I'd suggest you consider creating two documents, one with the always available data, and one with just the "_internal" fields. MongoDB will read and write less, and there will be less to manipulate on the client. This would be similar to having two tables in a RDBMS (one with public and one with private data).

This could make updates to the non-internal data simple as well by just updating the entire document (if possible in your scenario).

Of course, you could then also drop the extra "_" character as well, as that just adds extra unnecessary characters to the BSON data. :)

Recommend

  • Mongo collection query and Operators
  • Increase the speed of redrawing contour plot in matplotlib
  • Python timer script doesn't run when set to long periods later
  • Retrieve Windows Update history using WUAPILib from a remote machine
  • R mlogit on my data giving error 'system is computationally singular?
  • ObjectID generated by server on pymongo
  • Plot a decision tree with R
  • Reduction and collapse clauses in OMP have some confusing points
  • How can I extract results of aggregate queries in slick?
  • Needing to do .toArray() to get output of mongodb .find() on key name not value
  • MongoDb aggregation
  • Use of this Javascript
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • C++ Partial template specialization - design simplification
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Is there a javascript serializer for JSON.Net?
  • Read text file and split every line in MSBuild
  • C# - Serializing and deserializing static member
  • Java applet as stand-alone Windows application?
  • Javascript Callbacks with Object constructor
  • Validaiting emails with Net.Mail MailAddress
  • Where to put my custom functions in Wordpress?
  • Which linear programming package should I use for high numbers of constraints and “warm starts” [clo
  • Javascript + PHP Encryption with pidCrypt
  • Convert array of 8 bytes to signed long in C++
  • How to get next/previous record number?
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • Python: how to group similar lists together in a list of lists?
  • Buffer size for converting unsigned long to string
  • WPF Applying a trigger on binding failure
  • Error creating VM instance in Google Compute Engine
  • Understanding cpu registers
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Turn off referential integrity in Derby? is it possible?
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • Binding checkboxes to object values in AngularJs
  • How to load view controller without button in storyboard?