Menu
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)
853
When we use ->> operator of JSONB, PostgreSQL can use B-tree or Hash index for processing the operations. ->> operator returns the value of the specified attribute in text format. PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by the GIN JSONB operator class.
The answer is you can not do this. There are two types of index classes that can work on JSONB using GIST and GIN : the jsonb_path_ops , and jsonb_ops (the default). The default GIN operator class for jsonb supports queries with top-level key-exists operators ?, ?& and ?| operators and path/value-exists operator @>.
You can index JSON data as you would any data of the type that you use to store it. In particular, you can use a B-tree index or a bitmap index for SQL/JSON function json_value , and you can use a bitmap index for SQL/JSON conditions is json , is not json , and json_exists .
Because JSONB stores data in a binary format, queries process significantly faster. Storing data in binary form allows Postgres to access a particular JSON key-value pair without reading the entire JSON record. The reduced disk load speeds up overall performance. Support for indexing.
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))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With