There are plenty of reasons when NoSQL is the right answer. The biggest is when you care more about predictable performance: https://brooker.co.za/blog/2022/01/19/predictability.html?s=...
Which I don't. I'd rather see reliable operation than "predictable except for when it fails outright" in almost every situation.
If you've encountered that other situation, where failures are fine? Then great. But I still assert that's a tiny minority of real-life DB use cases.