Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a carts table with items hstore column. An example entry in this column is:

carts.items row#1 = {
                     "1614" => {:quantity=>"100", :price_cents=>1655},
                     "1938" => {:quantity=>"50", :price_cents=>1955},
                     "1983" => {:quantity=>"100", :price_cents=>2255},
                     "1322" => {:quantity=>"10", :price_cents=>4455},
                     "1691" => {:quantity=>"25", :price_cents=>1055},
                     "1734" => {:quantity=>"20", :price_cents=>1255}
                    }

carts.items row#2 = {"1614"=>{:quantity=>"50", :price_cents=>1655}}

So my carts table would look like this:

    id   | items  
---------+-------
    1    |   {"1614"=>{:quantity=>"100", :price_cents=>1655}, "1938" => {:quantity=>"50", :price_cents=>1955},"1983"=>{:quantity=>"100", :price_cents=>2255},"1322"=>{:quantity=>"10", :price_cents=>4455},"1691"=>{:quantity=>"25", :price_cents=>1055},"1734"=>{:quantity=>"20", :price_cents=>1255}}
    2    |   {"1614"=>{:quantity=>"50", :price_cents=>1655}}

You will notice that there is one duplicate id (1614) in the hash, but its quantity is different.

I want to write a query that will return a table with the item id counts and the total quantity. It should look like this:

 item_id | count | total 
---------+-------+------
   1614  |   2   |  150
   1938  |   1   |  50
   1983  |   1   |  50
   1322  |   1   |  100

Here is the query that I am working with:

SELECT  
skeys(carts.items) as item_ids,
COUNT(*) AS count,
svals(carts.items) as items
FROM carts
GROUP BY
skeys(carts.items),
svals(carts.items)

It returns:

 item_id | count | total 
---------+-------+------
   1614  |   1   |  {:quantity=>100}
   1614  |   1   |  {:quantity=>50}
   1938  |   1   |  {:quantity=>50}
   1983  |   1   |  {:quantity=>50}
   1322  |   1   |  {:quantity=>100}

I aslo have tried:

SELECT key, count(*) FROM
  (SELECT (each(items)).key FROM carts) AS stat
GROUP BY key
ORDER BY count DESC, key;

Which gives me this:

item_id | count 
---------+-------
   1614  |   2    
   1938  |   1   
   1983  |   1   
   1322  |   1   
share|improve this question
    
Typo in 1614 | 1 | {:quantity=>500}? –  Erwin Brandstetter Aug 28 '13 at 23:03
    
Thanks Erwin. Fixed typo. –  mpiccolo Aug 28 '13 at 23:33
    
What's with the weird syntax with multiple curly braces? That's not a valid hstore representation. –  Erwin Brandstetter Aug 29 '13 at 0:21
    
The syntax is ruby hash syntax. The ID is the key for the hash that contains quantity information as well as other stuff I did not include like pricing. What syntax does hstore use for nested objects? –  mpiccolo Aug 29 '13 at 0:28
    
I supplied a test case along with another solution for your second question. –  Erwin Brandstetter Aug 29 '13 at 0:38

1 Answer 1

Original question (dupes within hstore value)

I think the root cause of your problems is explained in this quote from the manual:

Each key in an hstore is unique. If you declare an hstore with duplicate keys, only one will be stored in the hstore and there is no guarantee as to which will be kept.

Bold emphasis mine.

You are mistaken in assuming that you could have the same key twice in a single hstore value.

I can also not reproduce your count of 2. I get a count of 1 for the key 1614. Start by not using count as column name. It is a reserved word (but allowed in Postgres).

I get (tested with Postgres 9.1.9):

WITH carts AS (
   SELECT '"1614"=>{:quantity=>"100"}, "1938"=>{:quantity=>"50"}, "1614"=>{:quantity=>"50"}, "1983"=>{:quantity=>"100"}, "1322"=>{:quantity=>"10"}, "1691"=>{:quantity=>"25"}, "1614"=>{:quantity=>"77"}, "1734"=>{:quantity=>"20"}'::hstore items
    )
SELECT key, count(*) AS ct FROM
  (SELECT (each(items)).key FROM carts) AS stat
GROUP BY key
ORDER BY ct DESC, key;

Result:

 key  | ct
------+----
 1322 |  1
 1614 |  1
 1691 |  1
 1734 |  1
 1938 |  1
 1983 |  1

Updated question (dupes across multiple rows)

To aggregate values you need a subquery (or CTE).

Simplified test case:

CREATE TEMP TABLE carts(c_id serial, items hstore); 
INSERT INTO carts(items) VALUES
   ('"1614"=>"100", "1938"=>"50", "1983"=>"100", "1322"=>"10", "1691"=>"25", "1734"=>"20"')
  ,('"1614"=>"50"');

Query:

SELECT item_ids, count(*) AS ct, sum(items) AS sum_items
FROM (
   SELECT (each(items)).key AS item_ids
         ,(each(items)).value::int AS items  -- assuming values can be cast to int
   FROM   carts
   ) sub
GROUP  BY 1           -- positional parameter is just a notational convenience
ORDER  BY 2 DESC;

Result:

 item_ids | ct | sum_items
----------+----+-----------
 1614     |  2 |       150
 1734     |  1 |        20
 1691     |  1 |        25
 1983     |  1 |       100
 1938     |  1 |        50
 1322     |  1 |        10
share|improve this answer
    
I am fairly new to postgres and DB in general. Each user can create a cart and a cart cannot contain two of the same item_id. So the example above is incorrect. Thank you for bringing that to my attention. The example really should be that there are two different carts entries and each have the same item id included. –  mpiccolo Aug 29 '13 at 0:08
    
Thanks for the answer. It seems like what your are getting at it the value of a key in hstore cannot be a nested hstore. Am I correct in inferring that is what you are saying with the updated question. The problem I have is the quantity is not the only value that the ID will be pointing to. There is also price like so: {:quantity=>5, :price_cents=>1655}. –  mpiccolo Aug 29 '13 at 18:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.