Skip to content

Instantly share code, notes, and snippets.

@jdforsythe
Created July 31, 2019 13:06
Show Gist options
  • Save jdforsythe/7d302e87f89fcf62b9d088c6979eb41c to your computer and use it in GitHub Desktop.
Save jdforsythe/7d302e87f89fcf62b9d088c6979eb41c to your computer and use it in GitHub Desktop.
PostgreSQL JOINs as nested JSON objects
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment