What an awful query example! This should and is simple. Here:
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;
Here: https://i.postimg.cc/zB1Bgg1L/movies.png