将JSON列更改为INTEGER[]数组

Question

将JSON列更改为INTEGER[]数组

sqlarraysjsonpostgresql

4

我有一个包含整数数组的JSON列。我试图将其转换为INTEGER[]列，但是遇到了类型转换错误。

这是我的最终修改版本：

ALTER TABLE namespace_list ALTER COLUMN namespace_ids TYPE INTEGER[] USING string_to_array(namespace_ids::text, ',');

然而，这会抛出以下错误： ERROR: cannot cast type json to integer[]

你有什么想法可以解决这个问题吗？我尝试了几种方法，但最终都遇到了相同的错误。似乎json -> string -> array的方式行不通。我的选择有哪些？

编辑：表定义：

db => \d+ namespace_list;

Column         |   Type   |  Table "kiwi.namespace_list" Modifiers|
---------------+----------+--------------------------------------+
id             | integer  | not null default nextval('namespace_list_id_seq'::regclass)
namespace_ids  | json     | not null default '[]'::json

示例数据:

id | namespace_ids | 
-------------------+
1 | [1,2,3]        |

- darksky

提供表定义和一些示例值是礼貌的做法。 - Erwin Brandstetter

@ErwinBrandstetter 添加了表定义；基本上就是描述的内容（一个 JSON 列），以及示例数据。 - darksky

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

假设您的数组中没有无效字符。

在Postgres 9.4或更高版本中，请按照以下方式使用转换函数：

如何将JSON数组转换为PostgreSQL数组？

CREATE OR REPLACE FUNCTION json_arr2int_arr(_js json)
  RETURNS int[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js)::int)';

ALTER TABLE namespace_list
  ALTER COLUMN namespace_ids DROP DEFAULT
, ALTER COLUMN namespace_ids TYPE int[] USING json_arr2int_arr(namespace_ids);

在这里使用db<>fiddle链接

对于Postgres 9.3或更早版本：

ALTER TABLE namespace_list
ALTER COLUMN namespace_ids TYPE INTEGER[]
      USING translate(namespace_ids::text, '[]','{}')::int[];

特别的困难在于你不能在USING子句中使用子查询表达式，因此无法对嵌套进行拆分和重新聚合。

SELECT ARRAY(SELECT(json_array_elements(json_col)::text::int))
FROM   namespace_list;

因此，我使用字符串操作来生成一个整数数组的有效字符串常量并进行强制转换。

列`DEFAULT`

如果您实际表定义中有像DEFAULT '[]'::json这样的列默认值，并且是在上述步骤之后添加的，请在执行上述步骤之前删除它。如果您需要一个新的DEFAULT，可以随后添加。最好在同一事务（甚至命令）中执行：

ALTER TABLE namespace_list
   ALTER COLUMN namespace_ids DROP DEFAULT
,  ALTER COLUMN namespace_ids TYPE INT[] USING translate(namespace_ids::text, '[]','{}')::int[]
,  ALTER COLUMN namespace_ids SET DEFAULT '{}';

db<>fiddle 这里
_{旧版sqlfiddle}

将JSON列更改为INTEGER[]数组

列DEFAULT

列`DEFAULT`