add index on jsonb field

14
votes

I need to search over the values of an array key of jsonb field in Postgres.

field: {'array_key' : [1, 2, 3, 4]}

Is it possible to add index on array_key or is there any optimized method to search over the values ?

search query will be something like

select * where field['array_key'].include?(2)
2
ruby-on-railspostgresqlindexingjsonb
can you add how your search query would look like? - ben
sure.. something like, select * where field['array_key'].includes?(2) - Raza

2 Answers

22
votes

You can create index on jsonb keys as,

add_index :table_name, :field, :using => :gin, :expression => "(field->'array_key')", :name => 'index_table_name_on_field_array_keys'

Then, you can search over indexed keys as,

where("table_name.field->'array_keys' @> ?", Array(2))
4
votes

given that you have a 'pictures' table with a 'metadata' jsonb column

add_index(
  :pictures,
  "(metadata->>'Year')",
  name: "index_pictures_on_metadata_year"
)

should output something like

CREATE  INDEX  "index_pictures_on_metadata_year" ON "pictures"  ((metadata->>'Year'))