将JSON转换为Postgres 9.3+中的HSTORE? [英] Cast JSON to HSTORE in Postgres 9.3+?

问题描述

我已经阅读了文档，并且似乎没有可辨别的方法来执行ALTER TABLE ... ALTER COLUMN ... USING语句以直接将json类型的列转换为hstore类型. (我知道)没有可用的功能来执行投射.

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.

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

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.

有更好的方法吗?

到目前为止，我有:

$ 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

推荐答案

不幸的是，PostgreSQL不允许ALTER TABLE ... SET DATA TYPE ...的USING子句中的所有类型的表达式(不允许子查询).

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

之后，您可以在ALTER TABLE中使用它，例如:

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

这篇关于将JSON转换为Postgres 9.3+中的HSTORE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！