I've been struggling with this one a lot - could someone explain where a document store is a good solution?
A document store flips the default. It makes dealing with data that has lots of nullable columns much, much easier. (It also makes dealing with hierarchical data a breeze)
There are lots of details, but this is the gist of it.
Certainly a lot of applications would benefit from having a full RDBMS they can opt-in to document-style data when they feel like it?
Built-in horizontal scaling is one selling point for non-RDBMS stores, but large systems seem to just shard on top of RDBMSes anyways, right?
It changes the default, which results in a drastically different programming experience. The difference is difficult to describe in the same way a dynamically typed programming language is difficult to describe to someone who's never tried one.
I'd encourage you to try a document store (Mongo, Rethink, whatever) for a throw-away project. A ten minute tutorial walkthrough is worth a thousand HN comments when it comes to stuff like this :)
Here's the Rethink tutorial: http://rethinkdb.com/docs/guide/python/. Just play with it and see if you like it!
Related: The comparison to a dynamically typed language makes me suspicious. I spent a bit of time trying to find any examples of dynamic code that actually provided any benefit. Even read "Metaprogramming Ruby" and was dismayed to see examples of reading a CSV - big deal if I save a few quotation marks. The others were just places where the static type system wasn't good enough (duck typing), or dynamic code was a pain to get going (poor reflection/codegen APIs).
Both document databases and dynamic typing are at their best when you don't understand your problem domain. They let you express what you do know about your problem domain concisely, and then fill in the blanks later on. So in a document database, when you find that you want to record a new bit of data - just add it as a field to newly-created XML/JSON documents, and only display it in the UI if it's present. Or pick a default value if you need to perform computations with it. Don't bother with data migrations, don't bother with schemas, don't bother trying to backfill previous data. Try out your idea and see if it works first, because chances are, it doesn't.
If you always work on projects where the requirements are handed to you, specs are complete, and the problem domain is understood, this will seem terribly irresponsible to you. And it is - if you understand your problem domain, you should capture as much of that knowledge in the software system you build to understand it.
But if you are working in startups, or in consumer web, where you absolutely have to be on the leading edge or die and the only opportunities that haven't been picked over yet are the ones that nobody understands - being able to try things out without having to flesh out all your assumptions is crucial. You will run circles around the people who spend time defining their data model and speccing out their objects. And then when consumer tastes change - which happens quite regularly - you can adapt to them immediately instead of throwing out all the work you did under the old assumptions.
The other bit of context I'll toss in is to get in the mindset of solving a problem that you don't know how to solve and assume that your first 10 solutions won't work. For example, if you're reading a CSV - everybody knows how to do that, dynamic typing doesn't really help there. If you're cloning Stack Overflow, you can probably figure out what your database schema should be. But what if you're trying to figure out a new way for people to socialize over mobile phones? Where do you start there? That's the use case for dynamic languages and document DBs. The problems where technology is a tool for understanding & manipulating vaguely-defined social behaviors.
In F#, adding a field requires "field : type". If I don't care about type checking, I can just add "Props : dict<string, object>" and go to town. Or I can opt-in to the dynamic features and just do "foo?bar <- baz". When I change types around, things either just work due to type inference, or the compiler helpfully points out every place that'd be a runtime error. I've never felt this slows me down. I feel the type checking and autocomplete is worth the tiny amount that specifying a record takes. (I've spent days finding minor issues in JavaScript, stuff that'd instantly be caught by a type checker.)
Databases make it a more cumbersome, and it takes more than one line to start using a new field. I totally sympathize with the flexibility issue there. Even with a document type, most syntax I've seen doesn't have truly first-class querying support (not as easy a column, anyways). And it feels ugly to have some fields defined in schema, and some in a document. But that seems like a minor tooling issue -- there's no fundamental reason SQL can't let me do "WHERE x.SomeDoc.SomeField.OtherField > 5" (perhaps some minor scope resolution issues to ensure I'm not referring to some other multi-part name).
When the schema is not rigid and likely to change on a daily basis I prefer a document database over an SQL one. There are probably other use cases as well, this is just my favourite.
If you're going to concatenate relational data into a document, I'm not sure why a simple KV table doesn't fit the job.