> > 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. >
OK. 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. JSON is of course a very light on formal/official standards, but some very strong de-facto standards have established themselves (e.g. ISO8601 for timestamps), and even beyond JSON, base64 seems to be the de-facto standard for encoding binary data as a string (which is what this is about). I'll also point out again that if the user really is looking only to get a string out and apply regular PG convert-from-string casting, they can do just that (i.e. omit RETURNING and apply regular PG casting); to me that points to RETURNING doing something beyond that, adding JSON-specific usefulness.
