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"

redneb · Accepted Answer · 2016-09-26 14:15:44Z

10

+50

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.

answered Sep 26, 2016 at 14:15

redneb

24.3k7 gold badges46 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brg Over a year ago

Thanks I will try this out and get back to you. But I will most likely place the index on the name column even though the jsonb column is email provider because the query will be faster when the index is on the name column and the where query is used to restrict our index to matches from the jsonb column. You can see that approach explained here: blog.heapanalytics.com/….

brg Over a year ago

Many thanks. I kept the index on the name column. The only change was to remove using: :gin since the index is not directly on the jsonb column. So final code for one of the indexes looks like this: add_index :accounts, :name, name: "index_accounts_on_name_email_provider", where: "(email_provider -> 'email_provider' -> 'sparkpost' ->> 'smtp_host' = 'www.sparkpost.com') AND (email_provider -> 'email_povider' -> 'amazon ses' ->> 'smtp_host' = 'www.awses.com')", which creates a btree partial index on the name column restricting our matches to attributes in the jsonb column.

Collectives™ on Stack Overflow

Create partial index for postgresql jsonb field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related