Porque simplicidade aparente cobra um preço. Só pessoas inexperientes acham que é simples.

NoSQL (termo ruim) é ótimo quando o problema encaixa perfeitamente nele. São raros os problemas que encaixam.

Tem problemas que são simples demais. Qualquer coisa que usar vai dar certo. Cada um vai cobrar um preço, mas pouco importa.

A maioria dos problemas complexos precisa de muita organização e cumprir certos objetivos, precisa de muito controle sobre as informações, e fácil se perder quando cria redundâncias, quando muita gente mexe. É aí que bancos de dados relacionais (se usa SQL ou não é outra questão) mostram seu valor. E os atuais conseguem fazer praticamente o mesmo que os NoSQL fazem quando é necessário em algum ponto. Você tem a opção, dá para usar de uma forma híbrida.

Essa aparente simplicidade se dá só em coisas muito simples onde os dados são praticamente só armazenados e nada mais se espera disso. Ou o modelo real dos dados é exatamente o modelo do banco de dados.

Não quero dar carteirada nisso, mas você acha que os profissionais mais experientes que fazem os sistemas mais complexos são todos trouxas por usar um banco de dados relacional na maioria dos projetos e só adotam NoSQL quando ele realmente é exigido e tem vantagem muito clara? Veja bem, estou falando de engenheiros estabelecidos, não influenciadores. Eu tenho acesso a muita gente muito boa, muito mais especialistas em bancos de dados, muitas vezes em qualquer tipo deles, gente com experiência brutal, com formação na área em alto nível, e todos preferem o relacional antes, e o NoSQL quando realmente ele se encaixa bem.

E até mesmo os maiores defensores de NoSQL falam que boa parte dos bancos nesse modelo, especialmente de documento, porque é o modelo onde as pessoas realmente usam errado, os outros quase sempre são bem indicados, especialmente em MongoDB (disparado o fornecedor mais usado nesse modelo), são modelados como se fosse relacional, mostrando claramente que o modelo deveria ter sido adotado. E só não gera grandes problemas porque é tão simples que não faz diferença. Quando não é, gera problema.

É frequente ver atrocidades usando NoSQL, porque a pessoa não vê a dificuldade que terá lá na frente quando os requisitos vão mudando. O relacional tem flexibilidade para atender esses casos.

Sem falar que essa facilidade de ficar mudando pode ser útil em algum ponto, mas vira pesadelo. É melhor ter coisas que precisam ser mais pensadas quando tem algo mais complexo. Ser fácil gera muita gambiarra. E nem é tão mais difícil assim mexer no relacional. Difícil é fazer dados redundantes para todo lado fazerem sentido em modelo de documento.

Veja mais e siga os links lá: https://pt.stackoverflow.com/q/274320/101 e https://pt.stackoverflow.com/q/576712/101.

E por coincidência, pouco depois de um postar isto, o Akita postou um vídeo sobre o assunto e conforma muito do que eu disse. Na verdade ele tem erros e discordo de várias coisas. Mas é assim mesmo. Outros vídeos dele também tem erros, conteúdos de todo mundo tem erros. E claro, o que eu faço também. Quem acha que não erra e que algumas pessoas não erram são os que cometem o pior dos erros. Isso não diminui o valor de coisa boa e a maioria das pessoas evoluirão muuuuuito vendo o vídeo. Um dia provavelmente faço um react e falo dos erros, nada grave. E aí alguém faz um com os erros que eu cometerei no meu.

Faz sentido para você?

Espero ter ajudado.

Farei algo que muitos pedem para aprender a programar corretamente, gratuitamente. Para saber quando, me segue nas suas plataformas preferidas. Quase não as uso, não terá infindas notificações (links aqui).

A resposta do @maniero é completíssima!!!

Um caso de uso:

• Precisávamos guardar uma parte dos históricos de procedimentos industriais que não podiam ser alterados. Essa parte correspondia a um "questionário" e precisavam ser basicamente uma linha do tempo, um "commit", imutável, mesmo se futuramente o "questionátio" mudasse, na linha do tempo deveríamos ter a pergunta e a resposta do usuário naquele momento. Foi decidido então usar um banco não relacional pra isso, e que no final foi implementado no DynamoDB da AWS.

Só uma observação: noSQL significa not only SQL e não no SQL, porque bancos não relacionais podem ou não ter SQL, por isso o not only (não apenas) SQL.