I have a table that has a jsonb column as 'result' which has an array of objects.

Looks like this for every row:

[{"grade": "A", "subject": "MATH"}, {"grade": "B", "subject": "PHY"}...]

When I am querying this way

select up.id, jsonb_array_elements(to_jsonb(up.result)) ->>'subject' as result 
from "UserProfile"

I get correct result but problem arises when I query for a particular row with an id wherein this result column has an empty array data. Then it neither gives any result nor an error even though I am selecting 'id' in query. not able to put any default value also using coalesce

How can I resolve this issue?

1 Answer 1


Use a left join:

select up.id, r.element ->>'subject' as result 
from "UserProfile" up
  left join jsonb_array_elements(up.result) as r(element) on true

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.