0

I am using postgress-9.3 with Rails 4.

I have a data type array of hstore(HSTORE[]).

Please see the normal query on product table.

SELECT id, specifications FROM "products" limit 10;

 id |                                              specifications                                              
----+----------------------------------------------------------------------------------------------------------
  1 | {"\"1368\"=>\"1\"","\"1368\"=>\"0.5\"", "\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
  2 | {"\"1368\"=>\"0.46\""}
  3 | {"\"1368\"=>\"1.5\""}
  4 | {"\"1368\"=>\"5\""}
  5 | {"\"1368\"=>\"3\""}
  6 | {"\"1368\"=>\"2\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
  7 | {"\"1368\"=>\"0.25\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
  8 | {"\"1368\"=>\"0.25\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
  9 | {"\"1368\"=>\"0.25\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
 10 | {"\"1368\"=>\"0.25\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}

My requirement is to Query on product table based on specification values, eg: Lists all product based on condition that 1368(KEY in HSTORE[]) is less than 1(VALUE in HSTORE[]).

# the result should 

id |                                              specifications                                              
----+----------------------------------------------------------------------------------------------------------
  1 | {"\"1368\"=>\"1\"","\"1368\"=>\"0.5\"", "\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
  2 | {"\"1368\"=>\"0.46\""}
  7 | {"\"1368\"=>\"0.25\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
  8 | {"\"1368\"=>\"0.25\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
  9 | {"\"1368\"=>\"0.25\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}
 10 | {"\"1368\"=>\"0.25\"","\"1371\"=>\"male/male\"","\"1370\"=>\"LAN (RJ-45)\"","\"1369\"=>\"LAN (RJ-45)\""}

How to implement this ?

Shamith c
  • 3,719
  • 3
  • 25
  • 33
  • An array of `hstore` isn't going to be super easy to work with. Have you considered normalizing this table structure a bit? – Craig Ringer Mar 20 '14 at 08:25
  • Thanks @CraigRinger. If i change HSTORE[] into JSON like `{"1368": [1, 0.5], "1371": ["male/male"] ,"1370": ["LAN (RJ-45)"], "1369": ["LAN (RJ-45)"]}`, is it possible?. Please give your input on this. – Shamith c Mar 20 '14 at 09:09
  • I was more thinking of *relational* normalization. What is the rationale for using arrays of hstore here in the first place? – Craig Ringer Mar 20 '14 at 09:27
  • Performance is the reason. if it workout, i can reduce the queries to filter the products based on specifications. Means I don't want to join the tables for filtering. – Shamith c Mar 20 '14 at 09:58
  • 1
    Have you actually tried doing it the normal way first? I'd be surprised if it didn't work better, especially with appropriate indexes. – Craig Ringer Mar 20 '14 at 10:00
  • I would consider a combination of [database normalization](https://en.wikipedia.org/wiki/Database_normalization#Normal_forms) as Craig already suggested and [`jsonb` in pg 9.4](http://stackoverflow.com/a/10560761/939860). – Erwin Brandstetter Dec 04 '14 at 03:54

0 Answers0