Convert json to text[] in alter table

Question

ALTER TABLE students
ALTER COLUMN hobbies type text[] using hobbies::text[];

It is showing an error that json cannot be cast into text[].

Answer 1

You need a helper function to do that.

create function json_to_array(json) returns text[] language sql as $$ 
    select array_agg(x) from json_array_elements_text($1) f(x) 
$$;

ALTER TABLE students
ALTER COLUMN hobbies type text[] using json_to_array(hobbies);