Get value from jsonb_array 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-&gt;'role' = R.id


Below is my column data.

{
    "live": "0",
    "role": [
        "1"
    ],
    "desig": "",
    "email": "chandan@logibricks.in",
    "empCode": "",
    "mobileNo": "",
    "password": "$2a$10$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"
}
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-&gt;'role') r on true
left join 
    role
    on role.id = r.value::int


db&lt;>fiddle here