Retrieving nested graphs from Postgres using JSON

ddl.sql

BEGIN TRANSACTION;
CREATE TABLE "user"
(
    "id" SERIAL PRIMARY KEY,
    "name" VARCHAR NOT NULL,
    "email" VARCHAR NOT NULL
);
CREATE TABLE "post"
(
    "id" SERIAL PRIMARY KEY,
    "text" VARCHAR NOT NULL,
    "image" VARCHAR NOT NULL,
    "author_id" INTEGER NOT NULL REFERENCES "user" ("id")
);
CREATE TABLE "comment"
(
    "id" SERIAL PRIMARY KEY,
    "post_id" INTEGER NOT NULL REFERENCES "post" ("id"),
    "author_id" INTEGER NOT NULL REFERENCES "user" ("id"),
    "text" VARCHAR NOT NULL
);

CREATE TABLE "post_like"
(
    "post_id" INTEGER NOT NULL REFERENCES "post" ("id"),
    "user_id" INTEGER NOT NULL REFERENCES "user" ("id"),
    PRIMARY KEY ("post_id", "user_id")
);

INSERT INTO
    "user"
VALUES
    (1, 'Rafal Stozek', '[email protected]'),
    (2, 'John Doe', '[email protected]');

INSERT INTO
    "post"
VALUES
    (1, 'This is awesome', 'awesome.jpg', 1),
    (2, 'Some graphs', 'graphs.jpg', 1),
    (3, 'Hello world', 'hello.jpg', 2);

INSERT INTO
    "comment"
VALUES
    (1, 1, 2, 'It is awesome, indeed'),
    (2, 1, 1, 'Thanks bro!'),
    (3, 2, 2, 'Nice graph!');

INSERT INTO
    "post_like"
VALUES
    (1, 2),
    (2, 1);

COMMIT;

queries.sql

-- load simple relation as JSON object
SELECT
    post.*,
    row_to_json(author.*) AS author
FROM
    post
JOIN author ON (author.id = post.author_id);

-- load sub-objects with simple aggregation
-- this sucks if you want more than one relation, as you must add a lot to GROUP BY (whole relations)
SELECT
    post.*,
    json_agg(row_to_json(comment.*)) as comments
FROM
    post
JOIN comment ON (comment.post_id = post.id)
GROUP BY post.id;

-- Load m2m relations as sub-queries.
-- Either load foreign key relations as json object with row_to_json/jsonb_build_object
-- or use aliases like 'author.id' and run returned object through something like dottie.js transformer.
-- m2m relations will be loaded as JSON arrays.
SELECT jsonb_pretty(jsonb_agg(r)) AS result
FROM (
    SELECT
        post.id,
        post.text,
        post.image,
        author.id AS "author.id",
        author.name AS "author.name",
        (post_like.user_id IS NOT NULL) as "viewerLiked",
        -- row_to_json(author.*) AS author,
        -- jsonb_build_object(
        --     'id', author.id,
        --     'name', author.name
        -- ) AS author,
        (
            SELECT json_agg(row_to_json(d)) AS comments
            FROM (
                SELECT
                    comment.*,
                    comment_author.id AS "author.id",
                    comment_author.name AS "author.name"
                    -- jsonb_build_object(
                    --     'id', author.id,
                    --     'name', author.name
                    -- ) AS author
                FROM comment
                JOIN "user" comment_author ON (comment.author_id = comment_author.id)
                WHERE post_id = post.id
                LIMIT 10  -- fetch no more than 10 comments!
            ) d
        )
    FROM post
    JOIN "user" author ON (author.id = post.author_id)
    LEFT JOIN "post_like" ON (post.id = post_like.post_id AND post_like.user_id = 1)
) r;