Postgresql - Return a column resulting from join as single json column

Question

I have a many to many relationship between users and roles table (A typical usecase). They are related together using a user_role table. I execute the below query:

select 
    u.id, u.first_name, u.last_name, u.middle_name,
    u.email_address, r.id, r.role_code
from 
master.user u
left join
master.user_role ur 
    on ur.user_id = u.id
    and ur.is_void = false
left join
master.role r
    on r.id = ur.role_id
    and r.is_void = false
where u.id = 7  and u.is_void = false

This results to

7;"Multi Role";"First";"Middle";"[email protected]";1;"ST"
7;"Multi Role";"First";"Middle";"[email protected]";2;"TC"

How do I aggregate the roles into one json or array column, such that it results to :

7;"Multi Role";"First";"Middle";"[email protected]";[{id: 1, role_code : ST}, {id: 2, role_code: TC}]

Please fix your example: In your query you have "Multi Role" at the column of "first_name". I think this is not correct, isn't it? — S-Man
– S-Man, Commented Oct 27, 2018 at 7:12

S-Man · Accepted Answer · 2018-10-27 07:16:33Z

3

demo: db<>fiddle

SELECT 
    id, 
    first_name, 
    last_name, 
    middle_name, 
    email_address, 
    jsonb_agg(
        jsonb_build_object('id', r_id, 'role_code', role_code)
    )
FROM 
    result_table
GROUP BY id, first_name, last_name, middle_name, email_address

Create a json object with jsonb_build_object, Postgres JSON functions
Aggregate the json objects with jsonb_agg, Postgres aggregate functions

Of course you can use type json instead of jsonb as well; Postgres JSON types

answered Oct 27, 2018 at 7:16

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql - Return a column resulting from join as single json column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related