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": "[email protected]",
    "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 Answer 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 and acknowledge you have read our privacy policy.

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