I use Postgres JSON functions to return nested results. The database itself contains no JSON; just a well-normalised data model. However, the queries return nested JSON in the format required by the application, e.g. for rendering an HTML template or returning JSON to the client in a single round trip. Check out the old dogs can sort of learn new tricks in this great article: https://www.scattered-thoughts.net/writing/sql-needed-struct...
> JSON functions to return nested results. The database itself contains no JSON; just a well-normalised data model. However, the queries return nested JSON in the format required by the application
Entirely valid usecase, since the client application is likely going to parse some cartesian product of tabular relationship data into "normalized" JSON array of objects anyways.
Generally, generating the JSON response directly for consumption in the DB is faster.
