Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE posts (
  id      int
, user_id int
, title   text
, slug    text
, content text
, views   int
);
INSERT INTO posts VALUES
  (1, 2, 'Foo', 'foo', 'bar', 0);

CREATE TABLE users(
  id int
, username text
, email text
, hash text
, role text
, auth text
);
INSERT INTO users VALUES
  (2, 'john', 'john@smith.com', 'aeigh*£HAEGhoiaehg', 'admin', 'aeg89hiae9g8hA*H£GA(*EHG');
1 rows affected

1 rows affected

SELECT row_to_json(combi, true) AS pretty_json_lvl1
FROM  (
   SELECT p.*, u AS user
   FROM   posts p
   JOIN   users u ON u.id = p.user_id
   WHERE  p.id = 1
   ) combi;


pretty_json_lvl1


{"id":1,
 "user_id":2,
 "title":"Foo",
 "slug":"foo",
 "content":"bar",
 "views":0,
 "user":{"id":2,"username":"john","email":"john@smith.com","hash":"aeigh*£HAEGhoiaehg","role":"admin","auth":"aeg89hiae9g8hA*H£GA(*EHG"}}


-- same result
SELECT row_to_json(combi, true) AS pretty_json_lvl1
FROM  (
   SELECT p.*, row_to_json(u) AS user  -- fruitless effort
   FROM   posts p
   JOIN   users u ON u.id = p.user_id
   WHERE  p.id = 1
   ) combi;


pretty_json_lvl1


{"id":1,
 "user_id":2,
 "title":"Foo",
 "slug":"foo",
 "content":"bar",
 "views":0,
 "user":{"id":2,"username":"john","email":"john@smith.com","hash":"aeigh*£HAEGhoiaehg","role":"admin","auth":"aeg89hiae9g8hA*H£GA(*EHG"}}


SELECT jsonb_pretty(to_jsonb(combi)) AS pretty_jsonb
FROM  (
   SELECT p.*, u AS user
   FROM   posts p
   JOIN   users u ON u.id = p.user_id
   WHERE  p.id = 1
   ) combi;


pretty_jsonb


{
    "id": 1,
    "slug": "foo",
    "user": {
        "id": 2,
        "auth": "aeg89hiae9g8hA*H£GA(*EHG",
        "hash": "aeigh*£HAEGhoiaehg",
        "role": "admin",
        "email": "john@smith.com",
        "username": "john"
    },
    "title": "Foo",
    "views": 0,
    "content": "bar",
    "user_id": 2
}


-- same result
SELECT jsonb_pretty(to_jsonb(combi)) AS pretty_jsonb
FROM  (
   SELECT p.*, to_jsonb(u) AS user  -- fruitless effort
   FROM   posts p
   JOIN   users u ON u.id = p.user_id
   WHERE  p.id = 1
   ) combi;


pretty_jsonb


{
    "id": 1,
    "slug": "foo",
    "user": {
        "id": 2,
        "auth": "aeg89hiae9g8hA*H£GA(*EHG",
        "hash": "aeigh*£HAEGhoiaehg",
        "role": "admin",
        "email": "john@smith.com",
        "username": "john"
    },
    "title": "Foo",
    "views": 0,
    "content": "bar",
    "user_id": 2
}