Skip to main content

Postgres searches

Searching on Postgres-specific column types.

Postgres Array searches​

See this issue for details.

PostgreSQL JSONB searches​

Using a fixed key​

See here for searching on a fixed key in a JSONB column: https://activerecord-hackery.github.io/ransack/going-further/ransackers/#postgres-columns

Using the JSONB contains operator​

To fully use the power of the JSONB column you may want to filter on any key though:

Install the ActiveRecordExtended gem to add the contains arel predicate to your project. It let's you use the Postgres contains operator @>.

Add a custom predicate in the config/initializers/ransack.rb file:

Ransack.configure do |config|
  config.add_predicate 'jcont', arel_predicate: 'contains', formatter: proc { |v| JSON.parse(v) }
end

Now you can ransack the JSONB columns using the _jcont predicate. For example the Person model has a data JSONB column, find entries where the column contains the {"group": "experts"} key-value pair:

Person.ransack(data_jcont: '{"group": "experts"}').result.to_sql

SELECT "persons".* FROM "persons" WHERE "persons"."data" @> '"{\"group\": \"experts\"}"'

If you have a GIN index on that column, the database will quickly be able to find that result.

Treating the column as a string​

Warning: This method converts the column to a string and matches the given string to the result. This will be slow on large data_sets and does not make good use of the JSONB capabilities of Postgres, such as indexes.

class Contact < ApplicationRecord
  ransacker :within_json do |parent|
    Arel.sql("table.jsonb_data::text")
  end
end

Contact.all.ransack("within_json_cont" => "my")

Will generate

SELECT "contacts".* FROM "contacts" WHERE contacts.json_data ILIKE '%my%'

Note that this search treats the entire JSON as string, including parens, etc. i.e. you can search for e.g.: Contact.all.ransack("within_json_cont" => '{"key": "value"}')