Re: subselect removes rows
| От | Pantelis Theodosiou |
|---|---|
| Тема | Re: subselect removes rows |
| Дата | |
| Msg-id | CAE3TBxwc+Uj5iSu6HXSwhn0KJm0Y23CTfSbkAQq7NsVh6jGZMA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: subselect removes rows (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-bugs |
On Mon, Nov 29, 2021 at 5:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Poot, Bas (B.J.)" <bas.poot@politie.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 ;
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 ;
В списке pgsql-bugs по дате отправления: