This shows the film with the highest score per genre:
SELECT DISTINCT ON (genre) *
FROM (
SELECT unnest(genres) AS genre, title, imdb_score
FROM tbl
) sub
ORDER BY genre, imdb_score DESC NULLS LAST;
db<>fiddle here
I think that's what you want.
Unnest the array with unnest().
About the set-returning function unnest() in the SELECT list:
You did not define how to break ties. (Multiple films with equally the highest score in the genre.) So this query picks an arbitrary winner.
About DISTINCT ON:
Since the imdb_score can be NULL, it's crucial to add NULLS LAST to the descending sort order. See:
If you insist on max() ...
Also returning all winning films per genre:
WITH flat AS (
SELECT unnest(genres) AS genre, imdb_score, title
FROM tbl
)
SELECT genre, title, imdb_score
FROM (
SELECT genre, max(imdb_score) AS imdb_score
FROM flat
GROUP BY 1
) sub2
JOIN flat f USING (genre, imdb_score)
ORDER BY genre, title;
Much slower and convoluted. To get all winners per genre, rather use the window function rank():
SELECT genre, title, imdb_score
FROM (
SELECT genre, title, imdb_score
, rank() OVER (PARTITION BY genre ORDER BY imdb_score DESC NULLS LAST) AS rnk
FROM (
SELECT unnest(genres) AS genre, imdb_score, title
FROM tbl
) sub
) sub2
WHERE rnk = 1
ORDER BY genre, title;
Does not eliminate winners with a NULL score - where no other score exists. (Unlike the solution with max().)
