To name just a few:
- being relaxed about schemas: no more long-running ALTER TABLE commands, no more up-front schema definitions that waste time when doing proof-of-concepts etc.
- being friendly to variable and hierarchical data: no more entity-attribute-value patterns and necessity to store JSON etc. as BLOBs
- integration of scripting languages such as JavaScript, so you can have one language for the full stack if you want
- embracing web standards (HTTP, JSON)
- no object-relational mismatch (there are no relations), as you can more easily map a single programming language object to a document
Relational databases partly offer solutions for this, too. But in a relational database, these things are (often clumsy) extensions and not well supported.
I do all of what you mention using SQL Express (I don't use JSON though, because binary serialisation is faster). Abstraction means I save my data as documents/blobs, can still do joins, don't have to alter tables (when de-serialised entities are self-describing), fully indexed entity content.
It means thinking about what and how you're going to do something before you start coding (design up front). It means creating a throw-away proof of concept before you start coding. But it is flexible, extensible, and changes to the schema, as it were, do not impact up-stream dependencies.
SQL Express guarantees consistency, but most NoSQL databases guarantee availability. Although many SQL databases can be used to implement the same functionality as a NoSQL database, that doesn't mean it's as easy. And easiness is what matters, because if something is easier, you may spend less time working on it and that saves money.
The term "NoSQL" database is a bit problematic, because the definition only says that the database is not relational, but the average NoSQL database has other differences with the average SQL database: using JSON and JavaScript, and perhaps queries with HTTP and so on.
The point is not "what is possible", but choosing the best tool for the job.
Indeed. Although I'd say most no-SQL databases guarantee partition tolerance (I can guarantee availability using a SQL database).
SQL Express runs as a single instance on a client so you get all three - consistency, availability and partition tolerance[1]. When running something else on more than one node you can choose two of those three. Availability and consistency are usually chosen because of business drivers. If partition tolerance is required (I've yet to encounter a scenario that makes a compelling case for it [2]) then eventual consistency is the price.
There are databases out there that will suite any combination of the three. Unfortunately the equation is somewhat more complex because databases that offer consistency and partition tolerance (for example) don't typically offer JOIN -like functionality.
Everything's a trade-off. Other considerations are tried and tested v. bleeding edge; painful v. painless; ideal v. affordable; and so forth.
[2] I'm not saying there aren't scenarios where it makes sense (Google, for example) - just that I've not encountered one. I do run into many people that want Mongo and, not understanding what they're asking for would be better of with a relational database.
I think it's not to say that no relational databases has any of these features.
But I think it's more exceptional than the rule.
Relational databases are primarily designed for relations. And saving your objects as documents simply is a different model.
That doesn't mean one is better than the other, it's two alternative ways of achieving things.
Relational databases partly offer solutions for this, too. But in a relational database, these things are (often clumsy) extensions and not well supported.