Nothing is wrong with NoSQL, used correctly and for the right purpose, it is AMAZING.
Most applications believe it or not are business modelling problems, which are overwhelmingly relational. SQL was invented to solve these, so no surprise it is actually the best tool for the job by far.
Absolutely. However, database is a sort of an extreme example. A lot in the modern software (especially Web) relies on the database, and often migrating to completely different one (because requirements change and it might not be the right tool anymore) is a huge task. So you want to use something flexible enough.
Also, you want to hire people, people leave the jobs, people change teams, etc. If you use some exotic, less common DB, it adds a lot of overhead. And if you apply the "right tool" to an extreme and have a few completely different DBs flying around, your maintenance cost increases a lot.
See, SQL might not be a perfect, most elegant choice, but most often it is just good enough. A lot of people have used it, a lot of people have scaled it. If you run into an issue, often enough,other people did too and blogged about it, etc. Hiring / getting help will be much easier than $insertNoSQLDBName.
And, let's be realistic, relatively few companies have hundreds of gigabytes or terabytes of data that typical relational DBs can't handle.
My rule of thumb is that if you're in doubt, use SQL/relational store (I realize that they are different things but often used as synonyms and mean MySQL/PostgreSQL/etc).
(This article certainly seems to be appealing to this use case, c.f. "counting arguments really carefully".)
A lot of recent "innovation" is mislabeled laziness.
Getting storage right is very hard. Either it's too low-level, and it's hard for applications to coordinate complex operations without corrupting data; or you end up putting a lot of features in and end up with a SQL dbms; or everything does its own storage and you have a mess.
First off, almost all of the complaints would have been valid years ago. Secondly, there is so much more choice out there today if mongodb wasn't the right answer for your project, and so many NoSQL stores have had time to mature and get polished APIs and docs.
We use various data stores for different purpose across microservices, mostly ES, couchbase, and datomic, and "use the right tool for the job" and "do one thing and do it well" feels like the right approach to take. For most applications, a SQL DB feels like a really big hammer that is put to a lot of things that don't look like nails.