How to nest json using SQL query?

Question

SQL

Hello.
--
I didn't think that I would degrade like this after php frameworks, but I realized that I can't remember how to nest through SQL queries. Just flat join 2 or more tables, no problem, JOIN and UNION make it easy. By nesting I mean this:

Can you show an example SQL query that does this nesting for json ?

Answer 1

DEMO
In general, nested subqueries or a chain of CTEs, each with its own level of aggregation.