Alter column type from text[] to jsonb[]

November 28, 2023

I have a text[] column which consists of elements of stringified json, and I want to convert it to jsonb[].

Here is my table shrunked to only relevant text[] column:

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

An example criterias column looks like this:

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

Each criteria is of the same format.

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

>Solution :

Basically, to_jsonb() converts a Postgres array into a JSON array automatically.

But your case is probably more convoluted. I guess you stored jsonb literals as elements of a text array (text[]). So you must cast each element to jsonb explicitly. To incorporate this into a single ALTER TABLE statement, I suggest a temporary function:

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

Then:

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

fiddle

To get an array of jsonb values (jsonb[]), use a function with array_agg() instead of jsonb_agg(), and 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
   );

The rest is mostly the same. jsonb[] is less commonly used.

fiddle

Note that either results in a complete table rewrite, which takes an exclusive lock on the table for the duration. See:

About temporary functions: