0

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": "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"
}
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.