MongoDB pro tip: field projections

Did you ever learn that select * from table in RDBMS-land is bad? Of course, you did! If you’re only looking for the email address of a user and not the other 15 columns worth of data, then why ask for that data and incur a penalty? The query select email from user where user_id = 1; is far more efficient for the database and the corresponding application that issued it, because there is less data to fetch and consume.

As it turns out, the same rule of thumb is true in MongoDB-land. That is, db.users.find({user_id:1}) is just as inefficient as the select * query if all you want is the user’s email address. With MongoDB, you can specify a projection as a part of your query that ultimately can limit what fields come back.

Thus, if I only want the email field on a user, I can issue a query like so:

MongoDB field projection
<span class='line-number'>1</span>
<code class='javascript'><span class='line'><span class="nx">db</span><span class="p">.</span><span class="nx">users</span><span class="p">.</span><span class="nx">find</span><span class="p">({</span><span class="nx">user_id</span><span class="o">:</span><span class="mi">1</span><span class="p">},</span> <span class="p">{</span><span class="nx">email</span><span class="o">:</span><span class="mi">1</span><span class="p">})</span>
</span></code>

The second clause specifies that you only want the email field returned. You can also negate fields by issuing a 0, which means false. To negate first_name and last_name, you would type:

MongoDB field negation with 0 or false
<span class='line-number'>1</span>
<code class='javascript'><span class='line'><span class="nx">db</span><span class="p">.</span><span class="nx">users</span><span class="p">.</span><span class="nx">find</span><span class="p">({</span><span class="nx">user_id</span><span class="o">:</span><span class="mi">1</span><span class="p">},</span> <span class="p">{</span><span class="nx">first_name</span><span class="o">:</span><span class="mi">0</span><span class="p">,</span> <span class="nx">last_name</span><span class="o">:</span><span class="mi">0</span><span class="p">})</span>
</span></code>

In this case, I’d get all fields on that user document except first_name and last_name. Note, you cannot issue both an include and exclude in the same statement.

For you Mongoid users, including specific fields translates to only and excluding them translates to without – each clause can be attached to a criteria (but not both at the same time). For example, if User is a Mongoid document and I only want an underlying query to grab the email field, then the corresponding Mongoid query would be:

Mongoid field projection
<span class='line-number'>1</span>
<code class='ruby'><span class='line'><span class="no">User</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">user_id</span><span class="p">:</span><span class="mi">1</span><span class="p">)</span><span class="o">.</span><span class="n">only</span><span class="p">(</span><span class="ss">:email</span><span class="p">)</span>
</span></code>

Field projection reduces document sizes on a fetch – this decreases memory consumption (for example, in the case of Mongoid your models aren’t fully populated with data) as well as bandwidth (that is, document retrieval is faster). Both MongoDB and the calling application benefit from field projection – it’s a win-win all the way around.

This story, "MongoDB pro tip: field projections" was originally published by JavaWorld.

Related:

Copyright © 2013 IDG Communications, Inc.