SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins (medium.com)

Nice !

I'm doing this even more extreme... querying a whole complex object graph based on an entity framework core model :) https://github.com/MUnique/OpenMU/blob/master/src/Persistenc...

Oracle has cursor expressions that returns a resultset inside a cell from the parent resultset. The whole resultset could then be serialized to json, xml or whatever

I wish there was an equivalent function in PostgreSQL. I've even asked a question about that on stackoverflow:

https://stackoverflow.com/questions/8637050/cursor-inside-sq...

BigQuery provides a TO_JSON_STRING function (https://cloud.google.com/bigquery/docs/reference/standard-sq...) that serves a similar purpose, e.g.:

    SELECT
      id,
      TO_JSON_STRING(ARRAY_AGG(item)) AS json
    FROM T
    GROUP BY id

The `item` can be a scalar type expression or a struct containing multiple fields, for example, so you can build JSON from structured data.

As a disclosure, I work on the BigQuery team.