Re: JSON_VALUE() behavior when RETURNING bytea (expected base64 decoding)

On Wednesday, March 5, 2025, Shay Rojansky <roji(at)roji(dot)org> wrote:

>
>>> SELECT JSON_VALUE(jsonb '"AQID"', '$' RETURNING bytea); -- Expected
>>> 0x010203, got AQID
>>>
>>
>> I get \x41514944 which is precisely what I would expect since it what
>> this query results in as well:
>>
>> select 'AQID'::bytea;
>>
>
> If the behavior of RETURNING is meant to be identical to that of simply
> applying a cast, is there any actual advantage in using JSON_VALUE with
> RETURNING? In other words, why not just do JSON_VALUE(json '"AQID"',
> '$')::bytea instead of using RETURNING? I thought the point was precisely
> for RETURNING to be able to perform JSON-specific conversions (e.g. take
> into account that the base64 is being converted from a *JSON* string, and
> therefore apply base64 decoding to it).
>

Not really…it does seem to just be syntactic sugar. Not that we’d be
likely to assume the contents of a JSON string are a base64 encoding as it
is just, as you claim, a de-facto standard. Unless we have some standard
(namely the one defining json_value) telling us that the contents are
indeed always base64 encoded data we’ll just assume it’s plain text and act
accordingly - in this case passing it into bytea’s input function.

David J.