Sql запрос, как сделать вложенные объекты?

Question

[syxme]

У меня есть несколько таблиц, group_city содержит список (city,group)

Добиться я хочу результата одним запросом что был таой json:

{
    id:1,
    email:"[e-mail]",
    pass:"hash",
    group_id:77,
    group:{
        id:77,
        name:"Супер группа",
        city:[
            {"id": 1, "name": "Moscow - Москва", "osm_id": 2555133, "coordinate": "(55.8097190000000012,37.834857999999997)"},
            {"id": 2, "name": "Chunga - changa", "osm_id": 111111, "coordinate": "(51.8097190000000012,15.834857999999997)"}
        ]
    }
}

Что-то я уже написал, но это не то.

SELECT
	"user".* as u,jsonb_agg("city".*) as city
FROM
	"user" 
JOIN "group" ON "user".id = "group".id
JOIN "group_city" ON "group".id = "group_city".group_id
JOIN "city" ON "city".id = "group_city".city_id
group by "user".id;

Answer 1

[syxme]

SELECT json_build_object(
               'id', w_user.id,
               'email', w_user.email,
               'pass', w_user.pass,
               'group', m_group,
               'roles', t_roles,
               'is_locked', w_user.is_locked,
               'created_at',w_user.created_at,
               'updated_at',w_user.updated_at
           ) as result
FROM
    w_user
    LEFT JOIN (SELECT w_group.id,
                      json_build_object(
                              'id', w_group.id,
                              'name', w_group.name,
                              'city', cities) as m_group
               FROM
                   w_group
                   LEFT JOIN (SELECT w_group_city.group_id, json_agg(w_city.*) as cities
                              FROM
                                  w_group_city
                                  LEFT JOIN w_city ON w_city.id = w_group_city.city_id
                              group by w_group_city.group_id) j_w_group_city
                             on w_group.id = j_w_group_city.group_id) j_w_group
              on w_user.group_id = j_w_group.id
    LEFT JOIN (SELECT t_user_roles.*, json_agg(t_user_roles.*) as t_roles
               FROM
                   w_user_roles t_user_roles
                   LEFT JOIN w_role ON w_role.id = t_user_roles.role_id
               group by t_user_roles.id) t_user_roles on w_user.id = t_user_roles.user_id