AFAIK relational algebra and relational calculus are based on FOL (first-order logic) which predicates atomic elements. For nested elements you would need HOL (higher-order logic) which is mathematically hard to optimize.

As FOL is as powerful as HOL but easier to optimise, it has become the basis for relational databases. The trade-off is that you have to normalise your data so that you can efficiently query and apply constraints to it.

But yes, just because your data at rest should be flat relations, that doesn't mean that the results of your queries need to be flat. I think querying a well-normalised database and returning nested JSON makes a lot of sense.

Sure, treating nested relations as “atoms” is supported naturally, but presumably you would want to query the content of these nested relations, which is where it gets complicated.

The straighforward translation of a hierarchical database into relational form is to represent nested structures as nested relation. But if you cant query these nested relations the database is useless. So you either need to normalize to 1NF or extend the query language to query nested relations.

You could reasonably want to evaluate data in those relations, to compose data to use at the top level. You could also reasonably run queries within the internal relations.

You could probably be able to promote those relations up on the hierarchy by using them in joins. But I don't think it's reasonable to mix queries of several levels. (I can't even imagine how the syntax for that would look like.)