Postgresql 将列类型从文本[]更改为jsonb[]

发布于11月28日

我有一个由字符串化的JSON元素组成的text[]列，我想将其转换为jsonb[].

下面是我的表格，减少到相应的text[]列:

CREATE TABLE certificate (
  ...
  criterias text[]
  ...
)

一个criterias列的例子看起来像这样:

'{"{\"url\":\"https://criteria.com\", \"description\":\"My Criteria\"}","{\"url\":\"https://criteria2.com\", \"description\":\"Other Criteria\"}"}'

每个标准的格式都相同.

I want to convert the type of the criterias column to be jsonb[].
How can I achieve this? I am using Postgres 15.4.

推荐答案

Basically, to_jsonb() converts a Postgres array into a JSON array automatically.
But your case is not so simple. You stored jsonb literals as elements of a text array (text[]). So you must cast each element (or the whole array) to jsonb explicitly.

对于完全有效的JSON文字，有一个以direct casts和text作为垫脚石的快捷方式.因为text的表示恰好是相同的.(不需要自定义功能.):

ALTER TABLE certificate
ALTER COLUMN criterias TYPE jsonb[] USING criterias::text::jsonb[];

fiddle

要转换为实际的jsonb列(更常用的设置)，请分别取消嵌套并强制转换元素.我建议设立一个临时功能:

CREATE OR REPLACE FUNCTION pg_temp.arr2jsb(text[])
  RETURNS jsonb
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN (
   SELECT jsonb_agg(elem::jsonb)
   FROM   unnest($1) elem
   );

然后:

ALTER TABLE certificate
ALTER COLUMN criterias TYPE jsonb USING pg_temp.arr2jsb(criterias);

fiddle个

要获取包含jsonb个值(jsonb[])的数组，请将函数与array_agg()(而不是jsonb_agg())和RETURNS jsonb[]一起使用:

CREATE OR REPLACE FUNCTION pg_temp.arr2jsb_arr(text[])
  RETURNS jsonb[]
  LANGUAGE SQL IMMUTABLE STRICT PARALLEL safe
RETURN (
   SELECT array_agg(elem::jsonb)
   FROM   unnest($1) elem
   );

fiddle个

其余的基本都是一样的.除了进行类型转换之外，执行更多操作可能很有用.否则，顶部的素色铸件就会更简单.

如果该列具有列DEFAULT，则可能需要删除并使用正确的类型重新创建该列.请参见: