/SQL/Postgresql display subquery as json

Postgresql display subquery as json

// PostgreSQL >= 9.4 : to_json(), json_build_object(), json_object() and json_build_array(), 
// though it's verbose due to the need to name all the fields explicitly:

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;

Source: stackoverflow.com

Postgresql display subquery as json

// joining 3 tables and nesting results as json, keys can be named arbitrarily

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;

Source: stackoverflow.com

Similar codes

14 votes268 views2 answers
Oracle add attribute to table
SQL
0 vote243 views1 answer
Mysql select last 15 minutes
SQL
2 votes258 views1 answer
Sql missing records from another table
SQL
0 vote280 views1 answer
Mysqli::query(): (HY000/1034): Index for table 'db' is corrupt; try to repair it
SQL
0 vote241 views1 answer
Time mysql w3
SQL
1 vote282 views1 answer
Mysql login console
SQL
0 vote240 views1 answer
Oracle select invalid views
SQL
4 votes263 views1 answer
Mysql copy table with new name
SQL
37 votes413 views3 answers
Sql check duplicate value in column
SQL
1 vote256 views1 answer
Job for mariadb.service failed because the control process exited with error code. see "systemctl status mariadb.service" and "journalctl -xe" for details.
SQL