Pgrel
ActiveRecord extension for querying hstore, array and jsonb.
Compatible with Rails >= 4.2 (including Rails 6).
Install
In your Gemfile:
gem "pgrel", "~> 0.3"HStore
Querying
The functionality is based on ActiveRecord WhereChain.
To start querying call where(:store_name) and chain it with store-specific call (see below).
Query by key value:
Hstore.where.store(:tags, a: 1, b: 2)
#=> select * from hstores where tags @> '"a"=>"1","b"=>"2"'
Hstore.where.store(:tags, a: [1, 2])
#=> select * from hstores where (tags @> '"a"=>"1"' or tags @> '"a"=>"2"')
Hstore.where.store(:tags, :a)
#=> select * from hstores where (tags @> '"a"=>NULL')
Hstore.where.store(:tags, { a: 1 }, { b: 2 })
#=> select * from hstores where (tags @> '"a"=>"1" or tags @> "b"=>"2"')Keys existence:
# Retrieve items that have key 'a' in 'tags'::hstore
Hstore.where.store(:tags).key(:a)
#=> select * from hstores where tags ? 'a'
# Retrieve items that have both keys 'a' and 'b' in 'tags'::hstore
Hstore.where.store(:tags).keys('a', 'b')
#=> select * from hstores where tags ?& array['a', 'b']
# Retrieve items that have either key 'a' or 'b' in 'tags'::hstore
Hstore.where.store(:tags).any('a', 'b')
#=> select * from hstores where tags ?| array['a', 'b']Values existence:
# Retrieve items that have value '1' OR '2'
Hstore.where.store(:tags).overlap_values(1, 2)
#=> select * from hstores where (avals(tags) && ARRAY['1', '2'])
# Retrieve items that have values '1' AND '2'
Hstore.where.store(:tags).contains_values(1, 2)
#=> select * from hstores where (avals(tags) @> ARRAY['1', '2'])Containment:
Hstore.where.store(:tags).contains(a: 1, b: 2)
#=> select * from hstores where tags @> '\"a\"=>\"1\", \"b\"=>\"2\"'
Hstore.where.store(:tags).contained(a: 1, b: 2)
#=> select * from hstores where tags <@ '\"a\"=>\"1\", \"b\"=>\"2\"'Update
Is implemented through ActiveRecord::Store::FlexibleHstore and ActiveRecord::Store::FlexibleJsonb
objects. You can get them by sending update_store(store_name) to relation or class.
Add key, value pairs:
Hstore.update_store(:tags).merge(new_key: 1, one_more: 2)
Hstore.update_store(:tags).merge([[:new_key, 1], [:one_more, 2]])Delete keys:
Hstore.update_store(:tags).delete_keys(:a, :b)Delete key, value pairs:
Hstore.update_store(:tags).delete_pairs(a: 1, b: 2)JSONB
All queries and updates for Hstore also available for JSONB.
NOTE. Querying by array value always resolves to (... or ...) statement.
Thus it's impossible to query json array value, e.g.:
Model.create!(tags: {main: ['a', 'b']})
Model.where.store(:tags, main: ['a', 'b']).empty? == true
#=> select * from models where (tags @> '{\"main\":\"a\"}' or tags @> '{\"main\":\"b\"}')Path query:
Model.create!(tags: {main: ['a', 'b'], user: { name: 'john' } })
# You can use object to query by simple value
Model.where.store(:tags).path(user: { name: 'john' })
#=> select * from hstores where tags#>>'{\"user\",\"name\"}' = 'john'
# or passing path parts as args one by one with value at the end
Model.where.store(:tags).path(:user, :name, 'john')
# Match by complex value (array or object)
Model.where.store(:tags).path(:main, ['a', 'b'])
#=> select * from hstores where tags#>'{\"main\"}' = '[\"a\",\"b\"]'Array
Array stores support containment queries (just like Hstore and JSONB) and also overlap operator.
NOTE. There are some other array operators ('ANY', 'ALL', querying by index - value) which I'm not going to implement – PRs are welcomed!
Overlap:
Model.where.store(:tags).overlap('a', 'b')
#=> select * from hstores where tags && '{\"a\",\"b\"}'Negation
Use not before operator to constuct negation or pass arguments to not to run key-value query.
Model.where.store(:tags).not.overlap('a', 'b')
#=> select * from hstores where not (tags && '{\"a\",\"b\"}')
Hstore.where.store(:tags).not(a: 1)
#=> select * from hstores where tags->'a' != '1'