There are primarily three mechanisms for building complex results:
XML Document Models (developed in Desinger)
JSON functions (8.3+)
XML Document Models are best suited when starting from an xml schema. They also have a concept of recursive subsections of the document. See the Designer docs for more.
SQL/XML and JSON functions are for creating XML and JSON repectively from just SQL statements. This is quite useful in may simplistic situations. Going off of the #2 example:
SELECT jsonObject(country.name, (SELECT jsonArray_agg(state.name order by state.name) FROM state where state.country_name = country.name) as "states") FROM country
or with a join and aggregation as:
SELECT jsonObject(country.name, jsonArray_agg(state.name order by state.name) as "states") FROM country left outer join state where country.name = state.country_name GROUP BY country.name
Either would produce a row result similar to:
The SQL/XML query would be similar, just with XMLELEMENT instead of jsonObject and an XMLAGG of XMLELEMENTS for the states. See the Reference for more on both options.
As for recursive situations, our SQL doesn't yet support recursive common table expressions so you'd likely have to explore a virtual procedure - but the procedure is also currently not allowed to call itself so you'd have to use properly designed loops. If you want, go ahead and log enhancement requests if there is a particlar style of recursion that would be useful.