Create combined index for JSONB column in PostgreSQL

user_1213273 0
Viewed 1145

I need to set a combined index on two fields from a JSONB in my PostgreSQL DB. I can set an index for a single field like so (using ActiveRecord in my Rails 6 application):

add_index :my_table,
  "(content->'reference')",
  using: :gin,
  name: 'index_my_table_on_content_reference'

This one works as expected. However, when I try to set a combined index for two fields, I get the following error:

add_index :my_table, 
  ["(content->'reference')", "(content->'ext_id')"], 
  using: :gin,
  name: 'index_my_table_on_content_ref_and_ext_id'

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column "(content->'reference')" does not exist

What am I doing wrong and how can I create a combined index for multiple fields in a JSONB column?

And before you ask: Yes, each JSONB blob has a key named reference.

Using: Ruby 2.6.5, Rails 6.0, PostgreSQL 11

jsonb indexing postgresql activerecord ruby-on-rails
2 Answers

The error PG::UndefinedColumn: ERROR: column "(content->'reference')" does not exist means it's treating "(content->'reference')" as a column name.

Reproducing from SQL:

create index my_table_json_idx on my_table using gin(
   (content->'reference'),
   "(content->'ext_id')"
);

Note the quotes around the second expression.

It seems there is a problem with your ActiveRecord library and it is escaping your jsonb expression when it isn't desirable.

Either use plain SQL or try to make your ActiveRecord library not escape your expression.

After some Googling I think changing the array to string "(content->'reference'), (content->'ext_id')" might work.

user_9274430

Ya Rails doesn't know how to dump your jsonb index using ruby. So SQL would be the way to go. This was a pretty good article for me when I started messing with jsonb Rails & jsonb

def change
  reversible do |dir|
    dir.up do 
      execute <<-SQL
       CREATE INDEX IF NOT EXISTS idx_my_table_on_content_references on my_table using gin ((content->>'reference'));
      SQL
    end

    dir.down {execute "DROP INDEX IF EXISTS idx_my_table_on_content_references;"}
  end
end
user_76195780
Related