How to make a IN query with hstore?

Question

I have a field (content) in a table containing keys and values (hstore) like this :

content: {"price"=>"15.2", "quantity"=>"3", "product_id"=>"27", "category_id"=>"2", "manufacturer_id"=>"D"}

I can easily select product having ONE category_id with :

SELECT * FROM table WHERE "content @> 'category_id=>27'"

I want to select all lines having (for example) category_id IN a list of value.

In classic SQL it would be something like this :

SELECT * FROM TABLE WHERE category_id IN (27, 28, 29, ....)

Thanks you in advance

Answer 1

De-reference the key and test it with IN as normal.

CREATE TABLE hstoredemo(content hstore not null);

INSERT INTO hstoredemo(content) VALUES 
('"price"=>"15.2", "quantity"=>"3", "product_id"=>"27", "category_id"=>"2", "manufacturer_id"=>"D"');

then one of these. The first is cleaner, as it casts the extracted value to integer rather than doing string compares on numbers.

SELECT * 
FROM hstoredemo 
WHERE (content -> 'category_id')::integer IN (2, 27, 28, 29);

SELECT * 
FROM hstoredemo 
WHERE content -> 'category_id' IN ('2', '27', '28', '29');

If you had to test more complex hstore contains operations, say with multiple keys, you could use @> ANY, e.g.

SELECT *
FROM hstoredemo
WHERE 
  content @> ANY(
    ARRAY[
      '"category_id"=>"27","product_id"=>"27"',
      '"category_id"=>"2","product_id"=>"27"'
    ]::hstore[]
  );

but it's not pretty, and it'll be a lot slower, so don't do this unless you have to.