json - 在 Postgres 9.3+ 中将 JSON 转换为 HSTORE？

Question

我已经阅读了文档，似乎没有明显的方法可以执行 ALTER TABLE ... ALTER COLUMN ... USING 语句来直接转换 json 类型列为 hstore 类型。没有可用的功能(据我所知)来执行转换。

我的下一个最佳选择是创建一个类型为 hstore 的新列，使用一些外部工具将我的 JSON 数据复制到该新列，删除旧的 json列并将新的 hstore 列重命名为旧列的名称。

有没有更好的办法？

我目前拥有的是:

$ 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 不允许在 ALTER TABLE ... SET DATA TYPE ... 的 USING 子句中使用所有类型的表达式(例如 sub - 不允许查询)。

但是，您可以编写一个函数来克服这个问题，您只需要决定如何处理高级类型(在对象的值中)，例如数组和对象。这是一个简单地将它们转换为字符串的示例:

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