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, DB2 (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.
