Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

create table table1 (
  id int,
  col1 text[]
);

insert into table1 values (1, '{"a:1", "b:2"}');

CREATE TABLE

INSERT 0 1

select id as table1_id,
       (string_to_array(ary, ':'))[1] as col1,
       (string_to_array(ary, ':'))[2] as col2
  from table1
       cross join lateral unnest(col1) as u(ary);

table1_id
col1
col2

1
a
1

1
b
2

SELECT 2