Casts from jsonb to other types should cope with json null

On Thursday, August 1, 2024, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Maciek Sakrejda <maciek(at)pganalyze(dot)com> writes:
> > Oddly, it looks like you only get a null if you use the '->>'
> > operator. With '->' and a subsequent cast to text, you get the string
> > "null":
>
> > maciek=# select (('{"a":null}'::jsonb)->'a')::text;
> > text
> > ------
> > null
> > (1 row)
>
> > Is that expected?
>
> 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 am fine with implementing the conversion of json null types to SQL null
for all casts that already do semantic value casting, and thus recognize
but prohibit the cast, as shown for float.

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”.

David J.

[1]
https://www.postgresql.org/message-id/flat/0154d35a-24ae-f063-5273-9ffcdf1c7f2e%40postgrespro.ru