Count items in arrays cross 100's of thousands of records

user_147586 0
Viewed 412

I have a Rails app with a Postgres database that has an Artists table with a jsonb genres column.

There are hundreds of thousands of rows.

Each genre column in the row has an array like ["rock", "indie", "seen live", "alternative", "indie rock"] with different genres.

What I want to do is output a count of each genre in JSON across all the rows.

Something like: {"rock": 532, "power metal": 328, "indie": 862}

Is there a way to efficiently do that?

Update...here's what I've got at the moment...

genres = Artist.all.pluck(:genres).flatten.delete_if &:empty?
output = Hash[genres.group_by {|x| x}.map {|k,v| [k,v.count]}]
final = output.sort_by{|k,v| v}.to_h

Output is a hash instead of JSON, which is fine.

But already feels pretty slow, so I'm wondering if there's a better way to do it.

postgresql ruby-on-rails ruby arrays json
2 Answers

On re-reading your question you state that the column IS a JSONb type. So the answer below will not work since you need to first get the array from the jsonb column. This should work better:

output = Artist.connection.select_all('select genre, count (genre) from (select id, JSONB_ARRAY_ELEMENTS(genres) as genre from artists) as foo group by genre;')

=> #<ActiveRecord::Result:0x00007f8ef20df448 @columns=["genre", "count"], @rows=[["\"rock\"", 5], ["\"blues\"", 5], ["\"seen live\"", 3], ["\"alternative\"", 3]], @hash_rows=nil, @column_types={"genre"=>#<ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Jsonb:0x00007f8eeef5d500 @precision=nil, @scale=nil, @limit=nil>, "count"=>#<ActiveModel::Type::Integer:0x00007f8eeeb4c060 @precision=nil, @scale=nil, @limit=nil, @range=-2147483648...2147483648>}> 

output.rows.to_h

=> {"\"rock\""=>5, "\"blues\""=>5, "\"seen live\""=>3, "\"alternative\""=>3}

As mentioned in the comments, if you can change the DB to normalize it, go for it. An anonymous array in a jsonb column is just going to be painful going forward. If you need to use this answer I would at least think about adding a view to the DB so that you can get the genre count as a table that has a corresponding model in rails (that you can just create in your model definitions).

Original answer when I thought your column was a regular array column type in Postgres.

Here is a SQL way to do it in Rails:

genre_count = Artist.connection.select_all('SELECT
                                   UNNEST(genres),
                                   COUNT (UNNEST(genres))
                                  FROM
                                   artists
                                  GROUP BY
                                   UNNEST(genres);')

You can then use the method of your choice to turn a much smaller dataset into JSON.

I am not familiar enough with UNNEST know why I can't alias it like any other column to make it prettier. But it works.

http://sqlfiddle.com/#!15/30597/21/0

user_27272670

This is an extremely trivial task if you just use a decent relational db design:

class Artist < ApplicationRecord
  has_many :artist_genres
  has_many :genres, through: :artist_genres
end

class Genre < ApplicationRecord
  has_many :artist_genres
  has_many :artists, through: :artist_genres
end

class ArtistGenre < ApplicationRecord
  belongs_to :artist 
  belongs_to :genre
end

You could then get the result by:

class Genre < ApplicationRecord
  has_many :artist_genres
  has_many :genres, through: :artist_genres

  # This will instanciate a record for each row just like your average scope
  # and return a ActiveRecord::Relation object.
  def self.with_artist_counts
    self.joins(:artist_genres)
        .select('genres.name, COUNT(artist_genres.id) AS artists_count')
        .group(:id)
  end

  # This pulls the columns as raw sql results and creates a hash with the genre 
  # name as keys
  def self.pluck_artist_counts
    self.connection.select_all(with_artist_counts.to_sql).inject({}) do |hash, row|
      hash.merge(row["name"] => row["artists_count"])
    end
  end
end
user_5448250
Related