JSON casting in HSTORE in Postgres 9.3+?

Question

I read the docs and it seems that there is no obvious way to execute the ALTER TABLE ... ALTER COLUMN ... USING statement to directly convert a JSON type column to an HSTORE type, since there is no function available that I know it will execute CAST operation.

The next best alternative is to create a new HSTORE column, copy the JSON data to this new column using some tool external to Postgres, delete the old JSON column and rename the new HSTORE column to the name of the old column.

Is there a better way?

What I still have:

$ CREATE TABLE blah (unstructured_data JSON);

$ ALTER TABLE blah ALTER COLUMN unstructured_data 
       TYPE hstore USING CAST(unstructured_data AS hstore);

ERROR:   cannot cast type json to hstore

Answer 1

, PostgreSQL USING ALTER TABLE ... SET DATA TYPE ... ( f.ex ).

, , ( ), . , :

CREATE OR REPLACE FUNCTION my_json_to_hstore(json)
  RETURNS hstore
  IMMUTABLE
  STRICT
  LANGUAGE sql
AS $func$
  SELECT hstore(array_agg(key), array_agg(value))
  FROM   json_each_text($1)
$func$;

ALTER TABLE, :

ALTER TABLE blah
  ALTER COLUMN unstructured_data
  SET DATA TYPE hstore USING my_json_to_hstore(unstructured_data);