how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result I have a table that has a jsonb column as 'result' which has an array of objects.
Looks like this for every row:
[{&quot;grade&quot;: &quot;A&quot;, &quot;subject&quot;: &quot;MATH&quot;}, {&quot;grade&quot;: &quot;B&quot;, &quot;subject&quot;: &quot;PHY&quot;}...]

When I am querying this way
select up.id, jsonb_array_elements(to_jsonb(up.result)) -&gt;&gt;'subject' as result 
from &quot;UserProfile&quot;

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?
Use a left join:
select up.id, r.element -&gt;&gt;'subject' as result 
from &quot;UserProfile&quot; up
  left join jsonb_array_elements(up.result) as r(element) on true