Re: subselect removes rows

On Mon, Nov 29, 2021 at 5:34 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Poot, Bas (B.J.)" <bas(dot)poot(at)politie(dot)nl> writes:
> > The problem is that I don't expect (and can't imagine it is correct)
> that a (any) sub-select removes rows from the resultset.
>
> I'm really not sure what you're saying here. If you're complaining
> about jsonb_each_text returning no rows for empty input, that behavior
> hasn't changed, and it's hard to see what else it could do. Your
> example isn't showing any other behavior that seems odd.
>
> regards, tom lane
>
>
As Tom explained, jsonb_each_text expands the json and may produce more
rows (if the json object has more than one items) or none (if it's empty or
null).
If you want something else, perhaps you can use a lateral join, to keep at
least one row always. Something like:

select
col1, col2, j.col2_item
from (
select 1 as col1, null::jsonb as col2
union all
select 1 as col1, '{"a":"2"}'::jsonb as col2
) t1
left join lateral
( select jsonb_each_text(t1.col2) as col2_item
) as j on true ;