Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE userdata (
  user_id integer
, username text
);

INSERT INTO userdata VALUES (1, 'james');


CREATE TABLE messages(
  message_id integer
, fk_messages_userdata integer
, message text
);

INSERT INTO messages VALUES
  (1, 1, 'hello')
, (2, 1, 'lorum ipsum')
, (3, 1, 'test123')
;

CREATE TABLE



INSERT 0 1



CREATE TABLE



INSERT 0 3


SELECT to_json(usr)
FROM  (
   SELECT u.user_id, u.username
       , (SELECT json_agg(msg)
          FROM (
            SELECT m.message_id, m.message
            FROM   messages m
            WHERE  m.fk_messages_userdata = u.user_id
            ) msg
         ) AS messages
   FROM   userdata u
   WHERE  u.user_id = 1  -- provide user_id here once!
   ) usr


to_json


{"user_id":1,"username":"james","messages":[{"message_id":1,"message":"hello"}, 
 {"message_id":2,"message":"lorum ipsum"}, 
 {"message_id":3,"message":"test123"}]}



SELECT 1


-- equivalent
SELECT json_build_object(
         'user_id', u.user_id
       , 'username', u.username
       , 'messages', (SELECT json_agg(msg)
                      FROM (
                        SELECT m.message_id, m.message
                        FROM   messages m
                        WHERE  m.fk_messages_userdata = u.user_id
                        ) msg
                      )
       )
FROM   userdata u
WHERE  u.user_id = 1  -- provide user_id here once!


json_build_object


{"user_id" : 1, "username" : "james", "messages" : [{"message_id":1,"message":"hello"}, 
 {"message_id":2,"message":"lorum ipsum"}, 
 {"message_id":3,"message":"test123"}]}



SELECT 1


-- pretty display (keys are sorted by jsonb!)
SELECT jsonb_pretty(to_jsonb(usr))
FROM  (
   SELECT u.user_id, u.username
       , (SELECT json_agg(msg)
          FROM (
            SELECT m.message_id, m.message
            FROM   messages m
            WHERE  m.fk_messages_userdata = u.user_id
            ) msg
         ) AS messages
   FROM   userdata u
   WHERE  u.user_id = 1  -- provide user_id here once!
   ) usr


jsonb_pretty


{
    "user_id": 1,
    "messages": [
        {
            "message": "hello",
            "message_id": 1
        },
        {
            "message": "lorum ipsum",
            "message_id": 2
        },
        {
            "message": "test123",
            "message_id": 3
        }
    ],
    "username": "james"
}



SELECT 1