Sql scripts for joining different tables

shared-server-selects.sql

-- Albums
select al.*, to_json(array_agg(distinct ar.*)) as artists, to_json(array_agg(distinct tr.*)) as tracks
from albums al
inner join albums_artists aa on al.id = aa.album_id
inner join artists ar on ar.id = aa.artist_id
inner join tracks tr on tr.album_id = al.id
group by al.id;

-- Tracks
select tr.*, to_json(array_agg(distinct ar.*)) as artists, to_json(array_agg(distinct al.*))::json->0 as album
from tracks tr
inner join albums al on al.id = tr.album_id
inner join artists_tracks art on art.track_id = tr.id
inner join artists ar on ar.id = art.artist_id
group by tr.id;

-- Artists
select ar.*, to_json(array_agg(distinct al.*)) as albums
from artists ar
left join albums_artists aa on ar.id = aa.artist_id
left join albums al on al.id = aa.album_id
group by ar.id;