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

On 06.03.25 11:55, Shay Rojansky wrote:
> For whatever it's worth, I'll note that SQL Server's OPENJSON does do 
> this (so when a JSON string property is extracted as a binary type, 
> base64 encoding is assumed). Other databases also have very specific 
> documented conversion rules for JSON_VALUE RETURNING (Oracle <https:// 
> docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/clauses- 
> used-in-functions-and-conditions-for-json.html#GUID- 
> DE9F29D3-1C23-4271-9DCD-E585866576D2>, DB2 <https://www.ibm.com/docs/en/ 
> i/7.3?topic=functions-json-table#rbafzscajsontable__json_result> (table 
> 1)). I'm basically trying to show that RETURNING definitely isn't a 
> simple cast-from-string in other databases, but is a distinct conversion 
> mechanism that takes into account the fact the the origin data comes 
> from JSON.

According to the SQL standard, once you account for various special 
cases (non-scalar values, null values), it comes down to a cast.