Databases like MongoDB are great when you usually know where your data is(as opposed to needing to write several complicated queries). With Mongo, "related" data is either nested in the parent data or it has primary/foreign keys. This is great if, for example, you have Posts and Comments; generally, you aren't going to be displaying comments outside the context of a post, so it makes sense that comments be contained within a post(that way you get all the comments for the post without needing to query a separate table).
MongoDB is schemaless. This means that it will take whatever structure of data you throw at it, for the most part.
On the other hand, if you are need to use aggregate functions and feel the need to query data in complex ways that cannot be achieved through embeds or simple relations in Mongo, that's when you know it's time to use a RDBMS like MySQL or PostgreSQL.
MongoDB isn't meant to replace SQL. It simply fulfills different needs, and MongoDB and an RDBMS can be used in conjunction. In my opinion, MongoDB isn't all that necessary if you don't need your data to be flexible or embedded in a parent document. Development with MongoDB is very fun because there are far fewer steps involved in getting a project(say in Rails) up and running. Need to make a change? No problem. Just add an attribute to your model. Done.
I can't speak for many other NoSQL databases, though I know that they are usually similarly designed to fulfill a specific need that cannot be met by an RDBMS. Some reside entirely in memory or are able to be sharded or scaled very easily. I'm pretty sure that Cassandra is designed to continue operating without data loss if a node goes down. Redis is basically a key value store that resides in memory(with periodic disk writes for persistence), but also has the ability to store data types like sets and sort them.