将JSON列更改为INTEGER [] ARRAY [英] Altering JSON column to INTEGER[] ARRAY

问题描述

我有一个包含整数数组的JSON列.我正在尝试将其转换为INTEGER []列，但遇到了转换错误.

I have a JSON column that contains an array of integers. I am trying to convert it to an INTEGER[] column, but I'm running into casting errors.

这是我的最终变更版本:

Here's my final alter version:

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

但是，这将引发以下错误: ERROR: cannot cast type json to integer[]

However, this throws this error: ERROR: cannot cast type json to integer[]

有什么想法可以实现这种转换吗?我已经尝试了几件事，但最终还是遇到了相同的错误.似乎去json->字符串->->数组不起作用.我有什么选择?

Any ideas how I can abouts this conversion? I've tried several things but I end up with the same error. Seems like going json --> string --> --> array does not work. What are my options?

表定义:

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]        |

推荐答案

如果您的数组中没有无效字符，则可以完成此工作:

This could do the job, if you have no invalid characters in your array:

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

-> SQLfiddle

特殊的困难在于，在USING子句中不能有子查询表达式，因此请取消&重新聚集是一种选择:

The specific difficulty is that you cannot have a subquery expression in the USING clause, so unnesting & re-aggregating is not an option:

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

因此，我求助于字符串操作以为整数数组生成有效的字符串常量并将其强制转换.

Therefore, I resort to string manipulation to produce a valid string constant for an integer array and cast it.

^{每个请求都在评论中}
我最初是自己构建测试用例的，因为您的表定义丢失了.因此，我错过了稍后添加的实际表定义中的default '[]'::json.
解决方案:在执行上述操作之前，将列默认值之前删除.我更新了小提琴.

^{Per request in comment}
I built the test case myself at first, because your table definition was missing. So I missed default '[]'::json in your actual table definition added later.
Solution: drop the column default before you do the above. I updated the fiddle.

ALTER TABLE namespace_list ALTER COLUMN namespace_ids DROP DEFAULT;

如果需要，可以在其后添加一个新的DEFAULT.最好在同一笔交易中，不要错过任何东西.

You can add a new DEFAULT afterwards if you need one. Best in the same transaction so not to miss anything.

这篇关于将JSON列更改为INTEGER [] ARRAY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！