how to extract all keys in json array into column when select in PostgreSQL-CodePudding

I'm using PostgreSQL version 12.9

I have a Table named EmployeeFamily and family column is type of jsonb

EmployeeFamily table is as follows:

id	first_name	last_name	family
1	A1	B1	[{"name":"C1","role":"Father"},{"name":"D1","role":"Mother"},{"name":"E1","role":"Brother"}]
2	A2	B2	[{"name":"C2","role":"Father"},{"name":"D2","role":"Mother"},{"name":"F2","role":"Sister"}]

Now I want a query with below result:

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

help me to write query with this result!

Thank's All

CodePudding user response：

You can use jsonb_array_elements to unfold the jsonb array.
Then get the fields from the family members.

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

Test on db<>fiddle here

CodePudding user response：

One option would be jsonb_to_recordset() function in order the unnest the array of objects such as

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)

Demo