Cast JSON to HSTORE in Postgres 9.3+?

Question

I've read the docs and it appears that there's no discernible way to perform an ALTER TABLE ... ALTER COLUMN ... USING statement to directly convert a json type column to an hstore type. There's no function available (that I'm aware of) to perform the cast.

The next best alternative I have is to create a new column of type hstore, copy my JSON data to that new column using some external tool, drop the old json column and rename the new hstore column to the old column's name.

Is there a better way?

What I have so far is:

$ 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

Unfortunately, PostgreSQL doesn't allow all kind of expressions within the USING clause of ALTER TABLE ... SET DATA TYPE ... (f.ex. sub-queries are disallowed).

But, you can write a function to overcome this, you just need to decide what to do with advanced types (in object's values), like arrays & objects. Here is an example, which simply converts them to string:

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$;

After that, you can use this in your ALTER TABLE, like:

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