SQL query for below statement

Question

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

Answer 1

You can unfold them with JSON_TABLE.

SELECT ids.Id, names.Name
FROM test t
CROSS JOIN JSON_TABLE(t.id,   '$[*]' COLUMNS(ordinal FOR ORDINALITY, Id INT PATH '$')) AS ids
LEFT JOIN  JSON_TABLE(t.name, '$[*]' COLUMNS(ordinal FOR ORDINALITY, Name TEXT PATH '$')) AS names
  ON names.ordinal = ids.ordinal;

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

db<>fiddle here

Answer 2

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.

Answer 3

PostgreSQL

Array Datatype

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)

JSON Datatype

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

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