I suspect it was mostly because the tree-like structures people were trying to represent are an absolute pain to work with when you have to shove them into two dimensional rows and columns. I doubt SQL itself had much to do with it as ORMs were already all the rage when Mongo emerged. But ORMs only slightly improve on papering over the data structure impedance mismatch. Most of the pain points present with using SQL directly remain in ORMs when it comes to this problem.

With Mongo you can just throw the tree at it and it will happily store it and nicely give it back again. Which eventually leads to its own set of problems due to how it handles said tree internally, but that's why it has fallen out of favour and SQL databases are the new hotness again.

You'll notice the frontrunners in the NoSQL movement of the time weren't relational databases with a different query language. They all took different approaches to dealing with data itself. Sometimes, humorously, they even maintained SQL as the query language. NoSQL didn't come to mean "No SQL" at all, but rather "Not Relational".

As an aside, while it might go a bit outside the spirit of SQL and introduces its own challenges, I remain somewhat amazed that we haven't seen a standard way emerge to query tree-like structures to better reflect the needs of a fairly common use case. You can kind of get there in unconventional ways, like using json_agg in Postgres, but that all seems pretty hacky.