Hstore Query to find records matching with any array element using ruby on rails

Question

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

cnnr · Accepted Answer · 2019-06-18 13:59:21Z

1

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';

answered Jun 18, 2019 at 13:59

cnnr

1,3074 gold badges18 silver badges23 bronze badges

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

2 Comments

kashif Over a year ago

Thanks, nice approach, but I'm just able to find a short way to perform this Query: User.where("sectors ?| Array['oil_and_gas', 'energy', 'transport']")

cnnr Over a year ago

@kashif Glad to help. I agree, your version is shorter.

Collectives™ on Stack Overflow

Hstore Query to find records matching with any array element using ruby on rails

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related