PostgreSQL | 9.2 row_to_json() with nested joins

postegre-row-to-json-with-nested-joins.md

SELECT
        json_build_object(
                'id', u.id,
                'name', u.name,
                'email', u.email,
                'user_role_id', u.user_role_id,
                'user_role', json_build_object(
                        'id', ur.id,
                        'name', ur.name,
                        'description', ur.description,
                        'duty_id', ur.duty_id,
                        'duty', json_build_object(
                                'id', d.id,
                                'name', d.name
                        )
                )
    )
FROM users u
INNER JOIN user_roles ur on ur.id = u.user_role_id
INNER JOIN role_duties d on d.id = ur.duty_id;

https://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins