Alter column type from text[] to jsonb[]

Question

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

Here is my table reduced to the 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.

Answer 1

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.

For completely valid JSON literals, there is a shortcut with direct casts and text as stepping stone. Because the text representation happens to be identical. (No custom function required.):

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

fiddle

To convert to an actual jsonb column (the more commonly used setup), unnest and cast elements individually. 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
   );

fiddle

The rest is mostly the same. Might be useful to do more than just type conversion. Else the plain cast at the top is simpler.

If the column has a column DEFAULT, you may have to drop and recreate that with proper type. See:

• Convert varchar column to inet

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

• Renaming multiple columns in one statement with PostgreSQL
• Will changing column from timestamp to timestamptz lock the table?

About temporary functions:

• How to create a temporary function in PostgreSQL?

About SQL-standard functions:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?