vbilopav

Create REST APIs for PostgreSQL databases in minutes.

select jsonb_build_object( 'title', title, 'genres', genres, 'actors', json_agg( jsonb_build_object( 'name', actor_name, 'characters', actor_characters ) ), 'directors', directors, 'writers', writers ) from ( select t.primaryTitle as title, t.genres, actor_person.primaryName as actor_name, array_agg(pc.character) as actor_characters, array_agg(director_person.primaryName) as directors, array_agg(writer_person.primaryName) as writers from title t left join principal actor on t.tconst = actor.tconst and actor.category = 'actor' left join person actor_person on actor.nconst = actor_person.nconst left join principal_character pc on actor.nconst = pc.nconst and actor.tconst = pc.tconst left join principal director on t.tconst = director.tconst and director.category = 'director' left join person director_person on director.nconst = director_person.nconst left join principal writer on t.tconst = writer.tconst and writer.category = 'writer' left join person writer_person on writer.nconst = writer_person.nconst where t.tconst = 'tt3890160' group by t.tconst, actor_person.primaryName, t.primaryTitle, t.genres, actor.ordering, director_person.primaryName, writer_person.primaryName order by actor.ordering asc ) main group by title, genres, directors, writers;