Using postgres arrays in Rails 4, how do I return an array of all the unique items in the array?

Question

My migration looks like this:

create table :posts do |t|
    t.string :tags, array: true, default: []
end

How can I get an array of all the unique tags stored under tags in all posts?

Answer 1

I believe you could also do this:

Post.pluck(:tags).flatten.uniq

If you have a ton of posts this may be a fairly significant hit on performance though...

Answer 2

As soon as I posted this, I found a working solution, although there may be a more elegant way of solving this:

Post.select(:tags).map(&:tags).flatten.uniq

I was being thrown off by the array of objects, solved by the .map.

Answer 3

You can do it inside the database with:

select distinct unnest(tags) from posts

So if you just want the strings then you can go straight to the database with:

tags = Post.connection.select_rows('select distinct unnest(tags) from posts').flatten

If there is a lot overlap amongst the tags arrays or a lot of arrays then that should be faster then pulling all the arrays out of the database and doing the data wrangling in Ruby.