Group by JSONB array in PostgreSQL

Recently, Nate Berkopec has been running an online webinar about profiling Rails applications. During the workshop, he was investigating the performance of a platform to recommend Spotify artists based on a listing history. PostgreSQL database was storing artists’ genres in an array structure using JSONB field.

An interesting case appeared in the middle of the session when Nate wanted to optimize a Ruby code to find the most popular genres based on the number of artists associated with them. As it turned out, it’s possible to delegate such a query to the database for better performance.

Let’s start with a schema definition and some sample data to play with:

CREATE TABLE artists (
  id SERIAL PRIMARY KEY,
  name TEXT,
  genres jsonb
);

To extract genres out of artists table, jsonb_array_elements function can be used:

SELECT jsonb_array_elements(artists.genres) AS genre
FROM artists;

Then the only part that left is to group, count and sort:

SELECT jsonb_array_elements(a.genres) AS genre, COUNT(1) AS popularity
FROM artists AS a
GROUP BY genre
ORDER BY popularity DESC;

Which gives the most popular genres:

Full example can be found on dbfidle.