How to convert one nested array to a simple array in PostgresSQL?

demo:db<>fiddle

Supposed you don't have a JSON array, you can use unnest() to extract all elements into one row and reaggregate these elements:

SELECT ARRAY(
    SELECT unnest(/*your array*/)
)

Further Reading:

• https://stackoverflow.com/a/25997497/3984221

demo:db<>fiddle

If you have JSON array, you can

1. use json_array_elements() do extract all elements

   SELECT
       json_array_elements(a)
   FROM (
       SELECT '[["one", "two", "three"]]'::json as a
   ) 
   

2. Or you simply use the -> operator to fetch the first element:

   SELECT
       a -> 0
   FROM (
       SELECT '[["one", "two", "three"]]'::json as a
   )s
   

Update all JSON data:

demo:db<>fiddle

UPDATE t
SET mydata = mydata -> 0;

I would need to update all the current values on the DB via an ALTER COLUMN

To update a column value, you use UPDATE not ALTER

The following will replace the value in the column with the "nested" array.

update the_table
  set the_column = the_column -> 0
where jsonb_typeof(the_column) = 'array'
  and jsonb_typeof(the_column -> 0) = 'array';

If the column is defined as json not as jsonb (which it should be), then you need to use json_typeof() instead.

You can get each 1D array from a 2D array with SLICE 1 and array_to_json() in a FOREACH statement as shown below, then you can use them for what you want.

DO $$
DECLARE
  temp VARCHAR[];
  _2d_arr VARCHAR[] := ARRAY[
    ['one', 'two', 'three'],
    ['four', 'five', 'six']
  ];
BEGIN       -- ↓ ↓ ↓ ↓
  FOREACH temp SLICE 1 IN ARRAY _2d_arr LOOP
    RAISE INFO '%', array_to_json(temp);
  END LOOP;      -- ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑
END
$$;

Then, you can get each 1D array from the 2D array as shown below:

INFO:  ["one","two","three"]
INFO:  ["four","five","six"]
DO

In addition, you can use SLICE 2 as shown below:

DO $$
DECLARE
  temp VARCHAR[];
  _2d_arr VARCHAR[] := ARRAY[
    ['one', 'two', 'three'],
    ['four', 'five', 'six']
  ];
BEGIN       -- ↓ ↓ ↓ ↓
  FOREACH temp SLICE 2 IN ARRAY _2d_arr LOOP
    RAISE INFO '%', array_to_json(temp);
  END LOOP;
END
$$;

Then, you can get the whole 2D array as shown below:

INFO:  [["one","two","three"],["four","five","six"]]
DO

And, you can use SLICE 0 without array_to_json() as shown below. *You need to change the type of temp local variable from VARCHAR[] to VARCHAR otherwise there is error:

DO $$
DECLARE
  -- temp VARCHAR[];
  temp VARCHAR; -- Here
  _2d_arr VARCHAR[] := ARRAY[
    ['one', 'two', 'three'],
    ['four', 'five', 'six']
  ];
BEGIN       -- ↓ ↓ ↓ ↓
  FOREACH temp SLICE 0 IN ARRAY _2d_arr LOOP
    -- RAISE INFO '%', array_to_json(temp);
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

Then, you can get each element from the 2D array as shown below:

INFO:  one
INFO:  two
INFO:  three
INFO:  four
INFO:  five
INFO:  six
DO