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

Question

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

Your JSON value looks very strange. Why the escaped "? Something is wrong there — user330315
– user330315, Commented Jan 30, 2022 at 12:45
I think you're looking for sequential id values rather than repeating ones in the result set, aren't you? — Barbaros Özhan
– Barbaros Özhan, Commented Jan 31, 2022 at 6:57

ashkan · Accepted Answer · 2022-01-31 05:32:46Z

4

In the first step, you should know that naming in this database is in lower case.
jsonb stores data in a decomposed binary form; that is, not as an ASCII/UTF-8 string, but as binary code.
in conclusion :

SELECT
    e.ID,
    e.first_name,
    e.last_name,
    j.MEMBER -> 'role' AS ROLE,
    j.MEMBER -> 'name' AS NAME 
FROM
    employeefamily e
    CROSS JOIN jsonb_array_elements (e.FAMILY) j(MEMBER)

answered Jan 31, 2022 at 5:32

ashkan

5901 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barbaros Özhan Over a year ago

What differs between this and the one from LukStorms?

LukStorms · Accepted Answer · 2022-01-30 13:28:55Z

2

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

edited Jan 30, 2022 at 13:28

answered Jan 30, 2022 at 13:17

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Comments

Barbaros Özhan · Accepted Answer · 2022-01-30 14:55:55Z

2

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

answered Jan 30, 2022 at 14:55

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related