I've a hstore field in a database table. I want to write a Query to find records matching with any array element in any hash of hstore field using ruby on rails.
Users Table
--------------------------------------------------------------------------------
ID Name sectors(hstore)
1 Piotr {"aviation"=>"0", "oil_and_gas" => "50", "transport" => "50"}
2 reza {"oil_and_gas" => "70", "energy" => "30"}
3 pat {"transport" => "40", "energy" => "60"}
4 Kim {"infrastructure" => "20", "healthcare" => "20", "industrial" => "60"}
considering above test data, I want to write a query on hstore field to get all records having any key like ['oil_and_gas', 'energy', 'transport']
I can match and find single sector records as its mentioned in https://nandovieira.com/using-postgresql-and-hstore-with-rails, but my requirement is to find any record where hstore hash is having any one key matching with any one element of array.
I'm using Rails 5.1.6.2, ruby 2.5.3
May be you are looking for the following operator:
hstore ? key # does hstore contain key?
Query may looks like so:
User.where("sectors ? 'oil_and_gas'")
.or("sectors ? 'energy'")
.or("sectors ? 'transport'")
According to postgresql docs
Check for a specific key in hstore column You can check for a specific key in an hstore column using the ? operator in the WHERE clause. For example, the following statement returns all rows with attr contains key publisher.
SELECT
title,
attr->'publisher' as publisher,
attr
FROM
books
WHERE
attr ? 'publisher';