为postgresql jsonb字段创建部分索引

Question

我试图向postgresql列添加一个部分索引。jsonb列名为：email_provider.我尝试在列上添加一个部分索引，如下所示，但是它引发了不同的错误，比如这个PG::UndefinedColumn: ERROR:列"email_povider“不存在，而在其他时候它会引发错误：PG::AmbiguousFunction: error not unique: unknown -> Uniting

rails-5迁移中的部分索引如下所示：

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')"

email_provider列的json如下所示：

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

这张桌子是这样的：

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

更新

根据对下面接受的答案的细微调整，下面显示了在rails activerecord中创建btree而不是gin索引的代码。它创建了一个btree索引，因为我们使用的是name列，它的字符串数据类型是，而不是这里描述的jsonb：

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

我想你需要这样的东西：

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')"

我将add_index的第二个参数更改为:email_provider，因为这是列名。另外，对于where子句，我更改了

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

至

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

因为->操作符希望它留下的参数是一个json(b)值，但是您提供了一个字符串。例如，email_provider -> 'email_provider'从名为email_provider的列中提取与email_provider对应的值。请注意，最后一行可以写得更简洁，如下所示：

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

通过使用从josn(b)对象中提取“路径”的#>>。因此，add_index语句可以编写为：

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')"

对于第二个索引，您应该尝试如下：

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

在本例中，我对列的想法与第一种情况相同。我还更改了@>的正确参数，它必须是一个jsonb值。因此，您必须将其定义为JSON字符串，它需要字符串的双引号(还请注意，对于ruby，我们必须转义它们)，然后我将该字符串强制转换为jsonb以获得所需的类型。