Get value from jsonb_array

Question

How can I get values of array in my jsonb type column named 'body' in order to use the value to join another table. example:-

SELECT U.* 
from masters."USERS" as U
 left join masters."ROLE" R on U.body->'role' = R.id

Below is my column data.

{
 "live": "0",
 "role": [
 "1"
 ],
 "desig": "",
 "email": "[email protected]",
 "empCode": "",
 "mobileNo": "",
 "password": "2ドルa10ドル$qDENcVR/ZDFIsp/blXK7h.h4xsmI18/LArvbqmtNfXxZ6jYO/s5YG",
 "userName": "chandan",
 "full_name": "Chandan ",
 "department": "",
 "updated_by": "rhlsoni",
 "is_access_results": "1",
 "updated_timestamp": 1550556834,
 "reporting_manager_id": "",
 "is_access_testscripts": "1",
 "is_access_workingpapers": "1"
}

Answer 1

You can expand the array using jsonb_array_elements_text() with a lateral join in this way:

select 
 users.id, 
 role.name
from 
 users
join lateral 
 jsonb_array_elements_text(data->'role') r on true
left join 
 role
 on role.id = r.value::int

db<>fiddle here