NoSQL vs Other SQL Drupal Setups
MongoDB can be used to store most or all your entities into fast, document-oriented storage. This type of storage scales way better then the standard SQL based storage we have in Drupal core (which is based on a "one table per field" schema).

In the current state of Drupal 7, you would have:


The base table of the entity stored on SQL (ie. the users table, the node table, etc.)
All fields stored in SQL
The properties of the entities from their base tables duplicated in MongoDB


This allows fast querying on the entities on MongoDB, and the ability to add complex indexes that no Opensource SQL database support (including indexes across tables). At the same time, you don't lose interoperability because the base table of the entity is still stored in SQL and can thus be joined by modules that are still SQL-only (like Flag).

This type of fast querying is available thanks to the EntityFieldQuery mechanism, a way to build queries on entities, their properties and their fields in an abstract manner. The default implementation in core translate those queries to SQL, but the MongoDB module has a full-featured implementation that can satisfy those queries from MongoDB directly.

Thanks to the EntityFieldQuery backend for Views, you can easily leverage this power, by using the tools you are used to. The only downside is that relationships are not supported (but in practice you rarely need them anyway - and this can be worked-around by pushing additional data into the entity object and add exposing them as additional properties of the entity).

In a nutshell, as soon as query performance is a problem on your project, which happens as soon as you have a significant dataset (let's say starting at a few tenth of thousands of entities on a given entity type), MongoDB is a net gain for very very few drawbacks. Highly recommended.