Postgres 12

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

CREATE TABLE EmployeeFamily 
(
 id         SERIAL  primary key, 
 first_name VARCHAR(50), 
 last_name  VARCHAR(50), 
 family     JSONB
)

CREATE TABLE


INSERT INTO EmployeeFamily(first_name,last_name,family) VALUES
('A1', 'B1', '[{"name":"C1","role":"Father"},{"name":"D1","role":"Mother"},{"name":"E1","role":"Brother"}]'),     
('A2', 'B2', '[{"name":"C2","role":"Father"},{"name":"D2","role":"Mother"},{"name":"F2","role":"Sister"}]')

INSERT 0 2


SELECT ROW_NUMBER() OVER (ORDER BY id, name ) AS id,
       first_name, last_name, name AS family_name, role
  FROM EmployeeFamily,
       jsonb_to_recordset(family) AS (name TEXT, role TEXT)


id
first_name
last_name
family_name
role


1
A1
B1
C1
Father

2
A1
B1
D1
Mother

3
A1
B1
E1
Brother

4
A2
B2
C2
Father

5
A2
B2
D2
Mother

6
A2
B2
F2
Sister



SELECT 6

id	first_name	last_name	family_name	role
1	A1	B1	C1	Father
2	A1	B1	D1	Mother
3	A1	B1	E1	Brother
4	A2	B2	C2	Father
5	A2	B2	D2	Mother
6	A2	B2	F2	Sister