From the beginning, I saw NoSQL as being great for prototyping or MVP. But the reality of our industry is that you very rarely get to bang something out overnight, then rewrite it the "right way". Your prototype or MVP more often than not becomes the foundation of your stable, mature releases.
Some projects may never need ad hoc queries because by the time they hit scale enough that they need such reporting they might go directly to BI tools (Cubes, Elastic Search, et al) or "Big Data" tools (Hadoop, Big Query, et al) without needing an intermediate SQL step.
The same is true with transactions and consistency models. Some projects may never have a need for anything stronger than eventual consistency and may never need more complicated transactions beyond the scope of an individual JSON document. Even then, there are chunky hybrid solutions with good options for choosing a different ACID plan or locking mechanics or transaction capabilities, and just because most "NoSQL" databases are in the corner of eventually consistent and transactions only at the individual document level does not mean that all are (there are definitely exceptions out there).
Again, there will always be projects and business domains on both sides of this "data spectrum". There's no wrong answer, just what's right for your specific project or business domain.
I think WorldMaker hit it on the head: do you plan your queries up front, or your data?
The spectrum seems to be ad hoc data on the one hand versus ad hoc queries on the other. Document databases prioritize being able to store just about any ad hoc document you might need at the expense of being able to quickly pull off new ad hoc queries that you didn't originally plan for. Relational databases prioritize being able to query very complex relationships in just about any way you can imagine at the expense of making it hard to add new shapes of data.
There's no right answer, just the answer that works best for you at the moment. The pendulum will probably keep swinging for a while, and we'll see more hybrids like we've been seeing Postgres and SQL Server embracing JSON more heavily, because the right answer is probably closer to the middle ground or at least "Why not both?".