Re: Casts from jsonb to other types should cope with json null

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Thursday, August 1, 2024, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think what is happening there is you're getting the fallback
>> "cast via I/O" behavior. There's no jsonb->text cast function
>> in the catalogs.
>> Perhaps it's worth adding one, so that it can be made to behave
>> similarly to the casts to other types.

> I’m not too keen on opening Pandora’s box here even if I do regret our
> current choices. Semantic casting of json scalar strings only, and doing
> document serialization as a function, would have been better in hindsight.
> ...
> I read the discussion thread [1] that added this and while one person
> mentioned json null no one replied to that point and seemingly no explicit
> consideration for treating json null semantically was ever done - i.e. this
> fails only because in json null has its own type, and the test were type,
> not value, oriented. As SQL null is a value only, whose type is whatever
> holds it, I’d argue our lack of doing this even constitutes a bug but
> wouldn’t - and turning errors into non-errors has a lower “bug acceptance
> threshold”.

Yeah, it's clear that this wasn't thought about too hard, because
I discovered that changing this behavior affects exactly zero existing
regression test cases :-(. But I still think we should probably
change it. Aside from ->>, we have other operators/functions that
convert jsonb values to SQL types, such as #>>,
jsonb_array_elements_text, jsonb_each_text, and AFAICS every one
of those translates JSON null to SQL NULL.

Attached are some actual patches for this. 0001 just changes the
existing json-to-scalar casts; since those previously threw
an error, I think that change is not too controversial. 0002 is
POC for changing the behavior of jsonb::text. I don't think
it's committable as-is, because we have the same behavior for
jsonb::varchar and other string-category target types. That's
not all that hard to fix, but I've not done so pending a decision
on whether we want 0002.

It strikes me that there's also a question of whether json::text
should translate 'null' like this. I'm inclined to think not,
since json is in some sense a domain over text, but it's certainly
debatable.

regards, tom lane