postgres_json_examples.sql

CREATE TABLE public.test (
	id SERIAL,
	username varchar NULL,
	name varchar NULL
);

INSERT INTO public.test (username, name)
VALUES
('Abhinickz','OWNER'),
('Abhasker','EMPLOYEE'),
('Abhishek','OWNER'),
('Bhasker','EMPLOYEE');

SELECT * FROM test;
/*
 id | username  |   name
----+-----------+----------
    | Abhinickz | OWNER
    | Abhasker  | EMPLOYEE
    | Abhishek  | OWNER
    | Bhasker   | EMPLOYEE
(4 rows)
*/

SELECT
    json_object_agg(
        name,
        data
    ) AS data
FROM (
    SELECT
        name,
        json_object_agg(
            id,
            username
        ) AS data
    FROM test
    GROUP BY name
) sq;
/*
                                                   data
-----------------------------------------------------------------------------------------------------------
 { "EMPLOYEE" : { "2" : "Abhasker", "4" : "Bhasker" }, "OWNER" : { "1" : "Abhinickz", "3" : "Abhishek" } }
(1 row)
*/