Search code examples
sqlpostgresqljsonb

Convert data from JSONB type to Array type in PostgreSQL

I have a table with columns in jsonb format which contains data like this:

['A', 'B', 'C']

I want to convert column to array column type but I don't know how to do it properly. Example of my script structure:

ALTER TABLE "products" ADD COLUMN "test_array" VARCHAR(2)[];

UPDATE "products" SET test_array = do converting here;

ALTER TABLE "products" DROP COLUMN "test";
ALTER TABLE "products" RENAME COLUMN "test_array" TO "test";
Solution

  • The syntax looks odd, but this works:

    update products 
   set test_array = array(select jsonb_array_elements_text(test));

    Working fiddle