-- jsonb_build_object
select
    jsonb_build_object(
        'id', s.id,
        'name', s.name,
        'address', jsonb_build_object(
            'city', jsonb_build_object(
                'name', s.addr_city,
                'state', s.addr_state,
                'country', s.addr_country
            ),
            'street', s.addr_street,
            'number', s.addr_number,
            'geo', jsonb_build_object(
                'latitude', s.addr_latitude,
                'longitude', s.addr_longitude
            )
        )
    ),
    s.id,
    s.name,
    to_json(s)
from
    norm.students s
;

-- dynamically working with jsonb_each and jsonb_object_agg - group by version
select
    t.id,
    jsonb_pretty(t.top_level || jsonb_build_object('address', t.address)) as student
from (
    select
        s.id,
        jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
        jsonb_object_agg(substr(e.key, 6), e.value) filter(where e.key like 'addr_%') as address
    from
        norm.students s
        , jsonb_each_text(to_jsonb(s)) as e
    group by
        s.id
) t
;

-- dynamically working with jsonb_each and jsonb_object_agg - lateral version
select
    s.id,
    jsonb_pretty(t.top_level || jsonb_build_object('address', t.address)) as student
from
    norm.students s
    cross join lateral (
        select
            jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
            jsonb_object_agg(substr(e.key, 6), e.value) filter(where e.key like 'addr_%') as address
        from
            jsonb_each_text(to_jsonb(s)) as e
    ) t
;

-- students + languages
select
    s.id,
    jsonb_pretty(jsonb_build_object(
        'id', s.id,
        'name', s.name,
        'address', jsonb_build_object(
            'city', jsonb_build_object(
                'name', s.addr_city,
                'state', s.addr_state,
                'country', s.addr_country
            ),
            'street', s.addr_street,
            'number', s.addr_number,
            'geo', jsonb_build_object(
                'latitude', s.addr_latitude,
                'longitude', s.addr_longitude
            )
        ),
        'languages', lang.languages
    ))
from
    norm.students s
    cross join lateral (
        select
            jsonb_agg(l.language) as languages
        from
            norm.students_languages l
        where
            s.id = l.student_id
    ) as lang
;

-- students + languages + grades
select
    s.id,
    jsonb_pretty(jsonb_build_object(
        'id', s.id,
        'name', s.name,
        'address', jsonb_build_object(
            'city', jsonb_build_object(
                'name', s.addr_city,
                'state', s.addr_state,
                'country', s.addr_country
            ),
            'street', s.addr_street,
            'number', s.addr_number,
            'geo', jsonb_build_object(
                'latitude', s.addr_latitude,
                'longitude', s.addr_longitude
            )
        ),
        'languages', lang.languages,
        'grades', grds.grades
    ))
from
    norm.students s
    cross join lateral (
        select
            jsonb_agg(l.language) as languages
        from
            norm.students_languages l
        where
            s.id = l.student_id
    ) as lang
    cross join lateral (
        select
            jsonb_agg(
                jsonb_build_object(
                    'code', g.code,
                    'grade', g.grade
                )
            ) as grades
        from
            norm.students_grades g
        where
            s.id = g.student_id
    ) as grds
;