Ruby on Rails has a good support for Postgres Array type. I really like using this feature when creating a separate database table sounds like over-engineering. In this short post I want to share my solution for the following problem: “find a record for which any of its tags contains a given string”.

Let’s use Book model with categories array field as an example. We start by generating a migration file:

1 rails g model Book name:text categories:text

and then we have to change it and specify array: true, default: [] .

1 2 3 4 5 6 7 8 9 10 class CreateBooks < ActiveRecord : :Migration [ 5 . 0 ] def change create_table :books do | t | t . text :name t . text :categories , array : true , default : [] t . timestamps end end end

As a next step we create a few records for testing:

1 2 3 Book . create! ( name : 'Traitors Of Time' , categories : [ 'science fiction' , 'romance' , 'drama' ] ) Book . create! ( name : 'Dead At The Beginning' , categories : [ 'romance' , 'horror' ] ) Book . create! ( name : 'Gangsters And Kings' , categories : [ 'action and adventure' , 'mystery' , 'drama' ] )

If we want to find records by the exact name of the category it is very simple. Postgres has it covered with ANY function:

1 Book . where ( ":name = ANY(categories)" , name : "drama" )

How about making our search a little bit more robust and supporting pattern matching? In case of a regular field we would do that using ILIKE :

1 Book . where ( "name ILIKE :name" , name : "%ing%" )

Unfortunately this approach does not work with ANY :

1 Book . where ( ":name ILIKE ANY(categories)" , name : "%action%" )

This query returns zero results, because the pattern containing % has to be on the right hand side of ILIKE .

1 Book . where ( "ANY(categories) ILIKE :name" , name : "%action%" )

And this is an unsupported syntax producing a syntax error.

Solution

We can work around this problem by using array_to_string function:

1 Book . where ( "array_to_string(categories, '||') ILIKE :name" , name : "%action%" )

array_to_string concatenates all the categories, using || as a separator between them and then we can use ILIKE in a usual way. The assumption here is that the pattern will never contain || .

This approach may not be the best idea if the number of rows is big. However, for a table with a couple hundreds rows it was the simplest solution I could come up with.