By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE result_table (
id int,
first_name text,
last_name text,
middle_name text,
email_address text,
r_id int,
role_code text
);
INSERT INTO result_table VALUES
(7, 'Multi Role', 'First', 'Middle', 'mult@gmail.com', 1, 'ST'),
(7, 'Multi Role', 'First', 'Middle', 'mult@gmail.com', 2, 'TC');
SELECT * FROM result_table;
2 rows affected
| id | first_name | last_name | middle_name | email_address | r_id | role_code |
|---|---|---|---|---|---|---|
| 7 | Multi Role | First | Middle | mult@gmail.com | 1 | ST |
| 7 | Multi Role | First | Middle | mult@gmail.com | 2 | TC |
SELECT
id,
first_name,
last_name,
middle_name,
email_address,
json_agg(json_build_object('id', r_id, 'role_code', role_code))
FROM
result_table
GROUP BY id, first_name, last_name, middle_name, email_address
| id | first_name | last_name | middle_name | email_address | json_agg |
|---|---|---|---|---|---|
| 7 | Multi Role | First | Middle | mult@gmail.com | [{"id" : 1, "role_code" : "ST"}, {"id" : 2, "role_code" : "TC"}] |