arrayspostgresqljsonbddlalter-table

Alter column type from text[] to jsonb[]

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.

Solution

  • 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:

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

    About temporary functions:

    About SQL-standard functions: