Input Both ID and Name column values are an array types.

 id name
 [1,2,3,4,5] [a,b,c,d]

Output

Id Name
1 a
2 b
3 c
4 d
5 null

CodePudding user response：

You can not do such thing directly from array. You must break them in parallel parts like you showed in the second diagram. Then you insert data one by by row.

CodePudding user response：

postgres=# create table arr(id int[],"name" char[]);
CREATE TABLE
postgres=# insert into arr values(array[1,2,3,4,5],array['a','b','c','d']);
INSERT 0 1
postgres=# select * from arr;
     id      |   name
------------- -----------
 {1,2,3,4,5} | {a,b,c,d}
(1 row)

postgres=# select unnest(id) id ,unnest("name") "name"  from arr;
 id | name
---- ------
  1 | a
  2 | b
  3 | c
  4 | d
  5 |
(5 rows)

CodePudding user response：

You can unfold them with json_table.

SELECT ids.Id, names.Name
FROM test t
CROSS JOIN LATERAL (
  SELECT Id, row_number() over () as ordinal
  FROM JSON_TABLE(t.id, '$[*]' COLUMNS(Id INT PATH '$')) jt
) ids
LEFT JOIN LATERAL (
  SELECT Name, row_number() over () as ordinal
  FROM JSON_TABLE(t.name, '$[*]' COLUMNS(Name TEXT PATH '$')) jt 
) names ON names.ordinal = ids.ordinal;

Id | Name
-: | :---
 1 | a   
 2 | b   
 3 | c   
 4 | d   
 5 | null

db<>fiddle here