Create combined index for JSONB column in PostgreSQL

Question

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

PG gives this error when the entire expression is escaped and treated like a column name. create index my_table_json_idx on my_table using gin((content->'reference'), "(content->'ext_id')");. Seems there is problem with ActiveRecord escaping when it isn't desirable. — Jindra
– Jindra, Commented Mar 26, 2020 at 10:08
@Jindra Thx for your feedback. So your suggestion would be to write the migration in plain SQL? — Severin
– Severin, Commented Mar 26, 2020 at 10:15
I do not know Ruby On Rails or your ActiveRecord library so I can't really help you there. Maybe there is a way to tell it to not escape. If there isn't then plain SQL. — Jindra
– Jindra, Commented Mar 26, 2020 at 10:18
@Jindra I ended up using plain SQL. If you want please post your suggestion as answer. — Severin
– Severin, Commented Mar 26, 2020 at 10:50

Jindra · Accepted Answer · 2020-03-26 11:19:35Z

1

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.

edited Mar 26, 2020 at 11:19

answered Mar 26, 2020 at 11:12

Jindra

81014 silver badges41 bronze badges

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

Comments

Int'l Man Of Coding Mystery · Accepted Answer · 2020-03-26 10:42:19Z

0

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

answered Mar 26, 2020 at 10:42

Int'l Man Of Coding Mystery

1,7122 gold badges16 silver badges36 bronze badges

2 Comments

Severin Over a year ago

This doesn't create a combined index, but I still see what you are getting at. Thx.

Int'l Man Of Coding Mystery Over a year ago

You may be able to create the clustered index the same way you do with regular columns. ((content->>'reference'), (content->>'ext_id'))

Collectives™ on Stack Overflow

Create combined index for JSONB column in PostgreSQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related