Question 211277 in PostgreSQL Question posted in
The official documentation can be found here.

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";
Tags:

3

Answers


  1. 0

    The JSONB to ARRAY conversion a bit tricky it can be done by unnest JSONB to rows using jsonb_array_elements_text and grouping back using array_agg functions:

    WITH _array AS (
    SELECT  id, array_agg(unnested_value) groupped_array 
    FROM (
        SELECT id, jsonb_array_elements_text(test) AS unnested_value
        FROM products
    ) groupped_array 
    GROUP BY id
)
UPDATE products
SET test_array = groupped_array
FROM _array
WHERE products.id = _array.id;

    Try SQL code here

    P.S. The conversion works when the table have primary key!

    Login or Signup to reply.
  2. 0

    I would suggest a helper function

    create or replace function json_array_to_array(j jsonb)
returns text[] language sql immutable as
$$
  select array_agg(el) from jsonb_array_elements_text(j) el;
$$;

    and then simply

    alter table products alter column test type text[] using json_array_to_array(test);

    The function may be useful in other cases too.

    Login or Signup to reply.
  3. 0

    The syntax looks odd, but this works:

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

    Working fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search