Aggregate Hstore Column in Postresql

Aggregate hstore column in PostreSQL

Building on Laurence's answer, here's a pure SQL way to aggregate the summed key/value pairs into a new hstore using array_agg and the hstore(text[], text[]) constructor.

http://sqlfiddle.com/#!1/9f1fb/17

SELECT hstore(array_agg(hs_key), array_agg(hs_value::text))
FROM (
  SELECT
    s.hs_key, sum(s.hs_value::integer)
  FROM (
    SELECT (each(goals)).* FROM statistics
  ) as s(hs_key, hs_value)
  GROUP BY hs_key
) x(hs_key,hs_value)

I've also replaced to_number with a simple cast to integer and simplified the key/value iteration.

Aggregate hstore in Postgres within GROUP BY

It is because of using views column without aggregate function in the group by query.

Very quick workaround:

with my_table(brand,account,likes,views) as (
  values
    ('Ford', 'ford_uk', 1, '"3"=>"100"'::hstore),
    ('Ford', 'ford_uk', 2, '"3"=>"200", "5"=>"10"'),
    ('Jeep', 'jeep_uk', 3, '"3"=>"300"'::hstore),
    ('Jeep', 'jeep_uk', 4, '"3"=>"400", "5"=>"20"'))
SELECT
  brand,
  sum(likes) AS total_likes,
 (SELECT hstore(array_agg(key), array_agg(value::text))
  FROM (
    SELECT s.key, sum(s.value::integer)
    FROM 
      unnest(array_agg(views)) AS h, --<< aggregate views according to the group by, then unnest it into the table
      each(h) as s(key,value)
    GROUP BY key
  ) x(key, value)) AS total_views
FROM my_table
GROUP BY brand

Update

Also you can to create the aggregate for such tasks:

--drop aggregate if exists hstore_sum(hstore);
--drop function if exists hstore_sum_ffunc(hstore[]);
create function hstore_sum_ffunc(hstore[]) returns hstore language sql immutable as $$
  select hstore(array_agg(key), array_agg(value::text))
  from
    (select s.key, sum(s.value::numeric) as value
     from unnest($1) as h, each(h) as s(key, value) group by s.key) as t
$$;
create aggregate hstore_sum(hstore) 
(
    SFUNC = array_append,
    STYPE = hstore[],
    FINALFUNC = hstore_sum_ffunc,
    INITCOND = '{}'
);

After that your query will be simpler and more "canonical":

select
  brand, 
  sum(likes) as total_likes,
  hstore_sum(views) as total_views
from my_table
group by brand;

Update 2

Even without create aggregate the function hstore_sum_ffunc could be useful:

select
  brand, 
  sum(likes) as total_likes,
  hstore_sum_ffunc(array_agg(views)) as total_views
from my_table
group by brand;

Can I use aggregate functions on PostgreSQL HStore values?

Yes. But values are stored as text, so you have to cast them to an appropriate data type first. So, to sum heights in inches, which are stored in an hstore in the column "other"

CREATE TABLE my_table (
  id integer primary key,
  other hstore
);
insert into my_table values 
(1, hstore('height_in', '72') || hstore('weight_lbs', '180')),
(2, hstore('height_in', '65') || hstore('girth_in', '42'));

select sum((other->'height_in')::integer) sum_of_height
from my_table;

sum_of_height
--
137

Merge hstore data from multiple rows

You can do a lot of things, f.ex:

My first thought was to use the each() function, and aggregate keys and values separately, like:

SELECT  hstore(array_agg(key), array_agg(value))
FROM    test,
LATERAL each(hs);

But this performs the worst.

You can use the hstore_to_array() function too, to build a key-value altering array, like (@JakubKania):

SELECT  hstore(array_agg(altering_pairs))
FROM    test,
LATERAL unnest(hstore_to_array(hs)) altering_pairs;

But this isn't perfect yet.

You can rely the hstore values' representation, and build up a string, which will contain all your pairs:

SELECT  hstore(string_agg(nullif(hs::text, ''), ','))
FROM    test;

This is quite fast. However, if you want, you can use a custom aggregate function (which can use the built-in hstore concatenation):

CREATE AGGREGATE hstore_sum (hstore) (
    SFUNC = hs_concat(hstore, hstore),
    STYPE = hstore
);

-- i used the internal function (hs_concat) for the concat (||) operator,
-- if you do not want to rely on this function,
-- you could easily write an equivalent in a custom SQL function

SELECT hstore_sum(hs)
FROM   test;

SQLFiddle

Postgres Aggregate Hstore

Use NULLIF to take care of empty strings:

'SUM(NULLIF("data"->\'total\', '')::numeric)) as "total"'

Can make the sum NULL as well, if all fields are NULL.

Update hstore values with other hstore values

There is nothing built int. You have to write a function that accepts to hstore values and merges them in the way you want.

create function merge_and_increment(p_one hstore, p_two hstore)
  returns hstore
as
$$
  select hstore_agg(hstore(k,v))
  from (
    select k, sum(v::int)::text as v
    from (
      select *
      from each(p_one) as t1(k,v)
      union all
      select *
      from each(p_two) as t2(k,v)
    ) x
    group by k
  ) s
$$
language sql;

The hstore_agg() function isn't built-in as well, but it's easy to define it:

create aggregate hstore_agg(hstore) 
(
  sfunc = hs_concat(hstore, hstore),
  stype = hstore
);

So the result of this:

select merge_and_increment(hstore('"key1"=>"1","key2"=>"1"'), hstore('"key1"=>"1","key2"=>"1","key3"=>"1"'))

is:

merge_and_increment                  
-------------------------------------
"key1"=>"2", "key2"=>"2", "key3"=>"1"

Note that the function will fail miserably if there are values that can't be converted to an integer.

With an insert statement you can use it like this:

insert into the_table (id, sum, data)
values (....) 
on conflict (id) do update
   set sum = the_table.sum + excluded.sum, 
       data = merge_and_increment(the_table.data, excluded.data);

Create check constraint on HSTORE to maintain specific succession of data

Create a function for checking the condition. Use the hstore function each() and aggregation function array_agg().

create or replace function check_episodes(hstore)
returns boolean language sql as $$
    select array_agg(key order by key::int) = array_agg(key order by value::date)
    from each($1)
$$;

create table archives_seasonmodel (
    episodes hstore check (check_episodes(episodes))
);

Db<>fiddle.

Group by Hstore Key in PostgreSQL and ROR application

People run into this occasionally when they move from MySQL. MySQL allows columns to be omitted from the GROUP BY clause. PostgreSQL does not. Your actual issue is that you are missing the state column. You have two options. If you want to pull just one state you can put it in an aggregate like this:

select max(state) , count(*) from infractions group by details -> 'commune';

If you want to group by state too you can:

select state , count(*) from infractions group by state, details -> 'commune';

How to find pairwise all combination in a Postgres hstore column?

Use the function akeys() to get all keys of the hstore column as an array, generate all distinct pairs of the arrays elements with generate_subscripts() and group the result by the pairs:

select akeys[i] as key1, akeys[j] as key2, count(*)
from my_table
cross join akeys(hstore_col)
cross join generate_subscripts(akeys, 1) as i
cross join generate_subscripts(akeys, 1) as j
where akeys[i] > akeys[j]
group by 1, 2
order by 1 desc, 2 desc

Test it in db<>fidlle.

---

Related Topics