There is a theory underlying it all: relational algebra, as you mentioned. The bolted on parts are just that. My point is discard those (XML and JSON in particular) and SQL is not at all unfriendly. It is in fact the friendliest way yet discovered to deal with data in large shared data banks. XML and JSON (as a model for how to store large amounts of data) are an absolute disaster by comparison. XML and JSON features exist in databases primarily to check a box on sales presentations. They aren't needed. If you are using them heavily, you are making your life more difficult than it needs to be. XML and JSON database features aren't completely useless. They are one of many tools you can use to get data out of XML and JSON and put it into normalized tables where it can be efficiently and elegantly stored, accessed, and otherwise dealt with in the future. The relational algebra subset of SQL (tables, rows, columns, primary keys, referential integrity constraints, check constraints, indexes, joins, where clauses, etc.) will get you very, very far.

My second point is that something about relational databases and SQL just doesn't sit well with (many) software developers. As a result, we have a lot of XML and JSON blobs stored in key/value databases, a lot of software that doesn't work very well, and we are still dealing with a lot of problems that Ted Codd solved 48 years ago.