CODEWITHSUNDEEP
postgresqljsonb
mhsankar
mhsankar

Reputation: 453

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

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

Upvotes: 4

Views: 5892

Answers (3)

ashkan
ashkan

Reputation: 580

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)

Upvotes: 4

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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

Upvotes: 2

LukStorms
LukStorms

Reputation: 29647

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

Upvotes: 2

Related Questions