Index jsonb column keys using GIN and pg_trgm, for ILIKE queries in Rails

I have a table "Leads" with the following structure :

# == Schema Information
#
# Table name: leads
#
#  id                       :integer          not null, primary key
#  data                     :jsonb            not null
#  state                    :string
#  priority                 :string
#  lead_no                  :string
#  user_id                  :integer
#  location_place_id        :string
#  uuid                     :string
#  agent_id                 :integer
#  location_coordinates     :geography        point, 4326
#  location                 :jsonb            not null
#  user_details             :jsonb            not null
#  inventory_id             :integer
#  source_details           :jsonb            not null
#  connect_details          :jsonb            not null
#  referral_details         :jsonb            not null
#  process_details          :jsonb            not null
#  tags                     :jsonb            not null
#  created_at               :datetime
#  updated_at               :datetime
#  name                     :string

The user_details jsonb column stores data in the form- {name : "John Doe", country : "IN", phone_no : "123456789"}. I want to query my database columns using ILIKE for the name key as :

Lead.where("user_details->>name ILIKE ?","john%")

To achieve this, I created a migration as shown:

class AddIndexUserNameOnLeads < ActiveRecord::Migration[5.2]
  def up
      execute("CREATE INDEX leads_user_details_name_idx ON leads USING gin((user_details->>'name') gin_trgm_ops)")
  end

  def down
    execute("DROP INDEX leads_user_details_name_idx")
  end
end

This creates the necessary index. I have already enabled the pg_trgm extension in a previous migration. My structure.sql looks like :

Also, the corresponding schema.rb adds the following line for leads table -

t.index "((user_details ->> 'name'::text)) gin_trgm_ops", name: "leads_user_details_name_idx", using: :gin

However, when I try to query my database, it does a sequential scan.

On the other hand,if I create a gin index for the entire user_details column and then query using "@> {name: "john"}.to_json" it uses index for scan

My Rails version is 5.2.0 and PostgreSQL version is 12.5. How can I use ILIKE queries for this use case? Where am I going wrong? I'll be happy to provide more details if necessary.

An alternative is to tell your index to already sort the values using either upper or lower case, so that you can simply use LIKE in your queries.

CREATE INDEX leads_user_details_name_idx ON leads 
USING gin(lower(user_details->>'name') gin_trgm_ops);

When querying this jsonb key you have to use the same function. Doing so the query planer will find your partial index:

SELECT * FROM leads
WHERE lower(user_details->>'name') ~~ '%doe%';

Demo: db<>fiddle