How can I convert an hstore[] column to jsonb?

Question

I have an hstore[] column that I want to convert to jsonb, but it seems that I cannot cast between the two types. (I can cast from plain hstore without issue).

For example, if I had '{foo=>bar,baz=>quux}'::hstore[] in a cell, I would want it to be converted to '[{"foo":"bar","baz":"quux"}]'::jsonb.

How can this be accomplished in postgresql?

Answer 1

Normally, you could use the USING clause of the ALTER TABLE ... ALTER COLUMN ... TYPE for conversions with extra logic (or for conversions, which has no explicit casts between their types).

The expression for SELECTing the transformed value is:

select (select json_agg(hstore_to_json(h)) from unnest(hstore_array) h)::jsonb
from   table_name

(Or, you could use hstore_to_json_loose(h) to get numbers & booleans instead of always getting strings.)

Unfortunately, you cannot use subselects in this USING clause , so you can do either:

1. create a temporary function & use that in USING

   create function temp_hstore_array_to_jsonb(hstore[])
     returns jsonb
     language sql
     immutable
   as $func$
     select json_agg(hstore_to_json(h))::jsonb
     from   unnest($1) h
   $func$;
   
   alter table table_name
     alter column hstore_array
     type jsonb
     using temp_hstore_array_to_jsonb(hstore_array);
   
   drop function temp_hstore_array_to_jsonb(hstore[]);
   
   -- optionally rename column
   

2. create another column, copy converted data & delete the original column

   alter table table_name
     add column jsonb_copy jsonb;
   
   update table_name
   set    jsonb_copy = (select json_agg(hstore_to_json(h))
                        from   unnest(hstore_array) h)::jsonb;
   
   alter table table_name drop column hstore_array;
   
   -- optionally rename column back like the dropped one
   

   But this later one has a weakness: the old column (to be dropped) might have dependent objects, which can make it harder to actually drop (CASCADE will drop them too, but that might not be what you want).