Create partial index for postgresql jsonb field

Question

I am trying to add a partial index to postgresql jsonb column. The jsonb column is named: email_provider. I have tried adding a partial index on the column as shown below but it throws different error like this PG::UndefinedColumn: ERROR: column "email_povider" does not exist and at other times it raises the error: PG::AmbiguousFunction: ERROR: operator is not unique: unknown -> unknown

The partial index in the rails-5 migration looks like this:

add_index  :accounts, :name, name:  "index_accounts_on_email_provider_kind", using: :gin, where: "('email_provider' -> 'sparkpost' ->> 'smtp_host' = 'www.sparkpost.com') AND ('email_povider' -> 'amazon ses' ->> 'smtp_host' = 'www.awses.com')"

The json for the email_provider column looks like this:

 {
   "email_provider": {
     "sparkpost": {
        "smtp_host": "www.sparkpost.com",
        "smtp_port": ""
      },
      "aws ses": {
        "smtp_host": "www.amazon/ses.com ",
        "smtp_port": " ",
        "username": " ",
        "password": " "
      }
  }
}

The table looks like this:

class CreateAccounts < ActiveRecord::Migration[5.0]
   def change
      create_table :accounts do |t|
        t.string :name, null: false
        t.jsonb :email_provider, null: false
        t.jsonb :social_account, default: '[]', null: false
        t.timestamps
   end
      add_index  :accounts, :name, name: "index_accounts_on_email_provider_kind", using: :gin, where: "('email_provider' -> 'sparkpost' ->> 'smtp_host' = 'www.sparkpost.com') AND ('email_povider' -> 'amazon ses' ->> 'smtp_host' = 'www.awses.com')"

      add_index  :accounts, :name, name: "index_accounts_on_social_account_type", using: :gin, where: " 'social_account' @> [{'type': 'facebook'}] AND 'social_account' @> [{'type': 'twitter'}]"
   end
end

Update

Based on slight adjustment to the accepted answer below, the code I am using to create btree not gin index in rails activerecord is shown below. It creates a btree index because we are using the name column which is of string datatype and not jsonb as described here :

add_index :accounts, :name, name:  "index_accounts_on_name_email_provider", where: "email_provider -> 'sparkpost' ->> 'smtp_host' = 'www.sparkpost.com' AND email_provider -> 'amazon ses' ->> 'smtp_host' = 'www.awses.com' "

add_index  :accounts, :name, name: "index_accounts_on_name_social_account", where: " social_account @> '[{\"type\": \"facebook\"}]'::jsonb AND social_account @> '[{\"type\": \"twitter\"}]'::jsonb"

Answer 1

I think you need something like that:

add_index :accounts, :email_provider, name:  "index_accounts_on_email_provider_kind", using: :gin, where: "(email_provider -> 'email_provider' -> 'sparkpost' ->> 'smtp_host' = 'www.sparkpost.com') AND (email_provider -> 'email_povider' -> 'amazon ses' ->> 'smtp_host' = 'www.awses.com')"

I've changed the second argument of add_index to :email_provider because that's the column name. Also, for the the where clause, I changed

'email_provider' -> 'sparkpost' ->> 'smtp_host' = 'www.sparkpost.com'

to

email_provider -> 'email_provider' -> 'sparkpost' ->> 'smtp_host' = 'www.sparkpost.com'

because the -> operator expects it left argument to be a json(b) value, but you provided a string. So e.g. email_provider -> 'email_provider' extracts the value corresponding to email_provider from the column called email_provider. Note that this last line can be written more compactly as:

email_provider #>> '{email_provider,sparkpost,smtp_host}' = 'www.sparkpost.com'

by using the #>> which extracts a "path" from a josn(b) object. So the add_index statement can be written as:

add_index :accounts, :email_provider, name:  "index_accounts_on_email_provider_kind", using: :gin, where: "(email_provider #>> '{email_provider,sparkpost,smtp_host}' = 'www.sparkpost.com') AND (email_provider -> '{email_povider,amazon ses,smtp_host}' = 'www.awses.com')"

For you second index, you should try something like:

where: " social_account -> 'social_account' @> '[{\"type\": \"facebook\"}]'::jsonb AND social_account -> 'social_account' @> '[{\"type\": \"twitter\"}]'::jsonb"

In this case, I did the same think with the column as in the first case. I also changed the right argument of @>, which has to be a jsonb value. So you must define it as a JSON string, which requires double quotes for strings (also notice that we have to escape them for ruby), and then I type cast that string to jsonb to get the desired type.