Postgresql transform column type from jsonb to array type enum

Question

I have column "scopes" which is jsonb type and contains array of strings ["admin","agent"]. I want to transform it to enum array type. I am using postresql.

This is my migration.

CREATE TYPE enum_scopes AS ENUM ( 'owner', 'admin', 'agent', 'user' );

ALTER TABLE public.users ALTER COLUMN scopes TYPE enum_scopes[] USING scopes::text::enum_scopes[];

After i run migration i get this: MigrationError: malformed array literal: "["admin"]"

Answer 1

You may first create a helper function like this

create or replace function jsonb_array_to_array(j jsonb) returns text[] as
$$
  select case 
    when j is null then '{}' 
    else (select array_agg(txt) from jsonb_array_elements_text(j) txt)
  end;
$$ language sql;

and then

ALTER TABLE public.users ALTER COLUMN scopes
  TYPE enum_scopes[] USING jsonb_array_to_array(scopes)::enum_scopes[];

The helper function is useful and generic enough to be reused in other cases too.