Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (
    data text[]
);

INSERT INTO t VALUES
(ARRAY['root_1', 'child_1', 'childchild_1']),
(ARRAY['root_1', 'child_1', 'childchild_2']),
(ARRAY['root_2', 'child_2']);

SELECT * FROM t;

CREATE TABLE



INSERT 0 3




data


{root_1,child_1,childchild_1}

{root_1,child_1,childchild_2}

{root_2,child_2}



SELECT 3


WITH RECURSIVE cte AS 
(  SELECT data[1] as title, 2 as idx, null as parent, data FROM t

   UNION

   SELECT data[idx], idx + 1, title, data
   FROM cte
   WHERE idx <=  cardinality(data)
)
SELECT DISTINCT
    title,
    parent
FROM cte


title
parent


child_2
root_2

childchild_2
child_1

root_1
null

childchild_1
child_1

root_2
null

child_1
root_1



SELECT 6


WITH RECURSIVE cte AS 
(  SELECT data[1] as title, 2 as idx, null as parent, data FROM t

   UNION

   SELECT data[idx], idx + 1, title, data
   FROM cte
   WHERE idx <=  cardinality(data)
)
SELECT 
    *,
    row_number() OVER ()
FROM (
    SELECT DISTINCT
        title,
        parent
    FROM cte
) s


title
parent
row_number


child_2
root_2
1

childchild_2
child_1
2

root_1
null
3

childchild_1
child_1
4

root_2
null
5

child_1
root_1
6



SELECT 6


WITH RECURSIVE cte AS 
(  SELECT data[1] as title, 2 as idx, null as parent, data FROM t

   UNION

   SELECT data[idx], idx + 1, title, data
   FROM cte
   WHERE idx <=  cardinality(data)
), numbered AS (
   SELECT 
       *,
       row_number() OVER ()
   FROM (
       SELECT DISTINCT
           title,
           parent
       FROM cte
   ) s
)
SELECT * 
FROM numbered


title
parent
row_number


child_2
root_2
1

childchild_2
child_1
2

root_1
null
3

childchild_1
child_1
4

root_2
null
5

child_1
root_1
6



SELECT 6


WITH RECURSIVE cte AS 
(  SELECT data[1] as title, 2 as idx, null as parent, data FROM t

   UNION

   SELECT data[idx], idx + 1, title, data
   FROM cte
   WHERE idx <=  cardinality(data)
), numbered AS (
   SELECT 
       *,
       row_number() OVER ()
   FROM (
       SELECT DISTINCT
           title,
           parent
       FROM cte
   ) s
)
SELECT 
    n1.row_number as id,
    n1.title,
    n2.row_number as parent_id
FROM numbered n1
LEFT JOIN numbered n2 ON n1.parent = n2.title


id
title
parent_id


2
childchild_2
6

4
childchild_1
6

6
child_1
3

1
child_2
5

3
root_1
null

5
root_2
null



SELECT 6
title	parent
child_2	root_2
childchild_2	child_1
root_1	null
childchild_1	child_1
root_2	null
child_1	root_1