Hacker News Reader

Postgres/Oracle supports such constructs.

And Revenj has been using it for years: https://github.com/ngs-doo/revenj

And yes, Revenj now comes with an offline compiler ;)

how can you do that with postgres ? a simple example / documentation link ?

SELECT p, array_agg(SELECT c FROM "MasterDetail"."Child_entity" c WHERE c."parentID" = p."ID") as c FROM "MasterDetail"."Parent_entity" p

Note, just replace array_agg with json_agg to get JSON.

And doing this the query iterates only once over the MasterDetail table ?

This particular query does a nested loop on the detail table, but basically yes. Only one iteration.