Get the number of rows in each row of hstore data in postgresql

Question

I have an hstore column in my postgresql database. Each row in the database has a different number of keys / values ​​in the hstore column. How can I get the number of keys / values ​​for each row?

Answer 1

select hstore_column, array_length(akeys(hstore_column), 1) as num_keys from the_table 

Answer 2

You can use one of hstore functions to convert hstore to another data type that can retrieve the number of elements. For example, you can use avalue to count the number of keys in an hstore value:

 SELECT id, array_length(avals(my_hstore_field), 1) AS count_keys FROM mytable; 

There is a good example in the docs to get all the keys and the number of occurrences that you might need:

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