Was the whole movement for using NoSQL databases for transactional databases a huge miss?

Ever since the dawn of NoSQL and everyone started using it as the default for everything, I've never really understood why everyone loved it aside from the fact that you could hydrate javascript objects directly from the DB. That's convenient for sure, but in my mind almost all transactional databases are inherently relational, and you spent way more time dealing with the lack of joins and normalization across your entities than you saved.

Don't get me wrong, document databases have their place. Also for a simple app or for a FE developer that doesn't have any BE experience it makes sense. I feel like they make sense at a small scale, then at a medium scale relational makes sense. Then when you get into large Enterprise level territory maybe NoSQL starts to make sense again because relational ACID DBs start to fail at scale. Writing to a NoSQL db definitely wins there and it is easily horizontally scalable, but dealing with consistency is a whole different problem. At the enterprise level though, you have the resources to deal with it.

Am I ignorant or way off? Just looking for real-world examples and opinions to broaden my perspective. I've only worked at small to mid-sized companies, so I'm definitely ignorant of tech at larger scales. I also recognize how microservice architecture helps solve this problem, so don't roast me. But when does a document db make sense as the default even at the microservice level (aside from specialized circumstances)?

Appreciate any perspectives, I'm old and I cut my teeth in the 2000's where all we had was relational dbs and I never ran into a problem I couldn't solve, so I might just be biased. I've just never started a new project or microservice where I've said "a document db makes more sense than a relational db here", unless it involves something specialized, like using ElasticSearch for full-text search or just storing json blobs of unstructured data to be analyzed later by some other process. At that point you are offloading work to another process anyway.

In my mind, Postgres is the best of both worlds with jsonb. Why use anything else unless there's a specific use case that it can't handle?

Edit: Cloud database services have clouded (haha) the conversation here for sure, cloud providers have some great distributed solutions that offer amazing solutions. Great conversation! I'm learning, let's all learn from each other.