postgresql display subquery as json

1// joining 3 tables and nesting results as json, keys can be named arbitrarily
2
3select
4        json_build_object(
5                'id', u.id,
6                'name', u.name,
7                'email', u.email,
8                'user_role_id', u.user_role_id,
9                'user_role', json_build_object(
10                        'id', ur.id,
11                        'name', ur.name,
12                        'description', ur.description,
13                        'duty_id', ur.duty_id,
14                        'duty', json_build_object(
15                                'id', d.id,
16                                'name', d.name
17                        )
18                )
19    )
20from users u
21inner join user_roles ur on ur.id = u.user_role_id
22inner join role_duties d on d.id = ur.duty_id;
23

1// PostgreSQL >= 9.4 : to_json(), json_build_object(), json_object() and json_build_array(), 
2// though it's verbose due to the need to name all the fields explicitly:
3
4select
5        json_build_object(
6                'id', u.id,
7                'name', u.name,
8                'email', u.email,
9                'user_role_id', u.user_role_id,
10                'user_role', json_build_object(
11                        'id', ur.id,
12                        'name', ur.name,
13                        'description', ur.description,
14                        'duty_id', ur.duty_id,
15                        'duty', json_build_object(
16                                'id', d.id,
17                                'name', d.name
18                        )
19                )
20    )
21from users u
22inner join user_roles ur on ur.id = u.user_role_id
23inner join role_duties d on d.id = ur.duty_id;
24