PostgreSQL JOINs as nested JSON objects

join-json.sql

-- returns a child object with single parent as nested JSON object
--
-- {
--   "id": 155,
--   "name": "Stephen King",
--   "publisher": {
--     "id": 13,
--     "name": "Paladin Books"
--   }
-- }
--
SELECT
  p.id
  ,p.name
  ,json_build_object(
    'id', a.id
    ,'name', a.name
  ) AS author
FROM author a
INNER JOIN publisher p on p.id = a."publisherId";

-- returns a parent object with multiple children as a nested JSON array
--
-- {
--   "id": 13,
--   "name": "Paladin Books",
--   "authors": [
--     {
--       "id": 155,
--       "name": "Stephen King"
--     },
--     {
--       "id": 175,
--       "name": "Friedrich Nietzsche"
--     }
--   ]
-- }
--
SELECT
  p.id
  ,p.name
  ,json_agg(
    json_build_object(
      'id', a.id
      ,'name', a.name
    )
  ) AS authors
FROM publisher p
INNER JOIN author a ON a."publisherId" = p.id
GROUP BY
  p.id
  ,p.name;