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(30), 
 last_name varchar(30), 
 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 emp.id, emp.first_name, emp.last_name
, fam.member->>'name' as family_name
, fam.member->>'role' as role
from EmployeeFamily emp
cross join lateral jsonb_array_elements(emp.family) fam(member)


id
first_name
last_name
family_name
role


1
A1
B1
C1
Father

1
A1
B1
D1
Mother

1
A1
B1
E1
Brother

2
A2
B2
C2
Father

2
A2
B2
D2
Mother

2
A2
B2
F2
Sister



SELECT 6

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