Greetings hackers,
The de-facto standard for storing binary data in JSON documents seems to be base64-encoded strings, so I was expecting JSON_VALUE's RETURNING bytea to do base64 decoding. However, that does not seem to be the case:
SELECT decode('AQID', 'base64'); -- 0x010203
SELECT JSON_VALUE(jsonb '"AQID"', '$' RETURNING bytea); -- Expected 0x010203, got AQID
This forces an additional explicit decode() function call:
SELECT decode(JSON_VALUE(jsonb '"AQID"', '$'), 'base64'); -- 0x010203
Is the above behavior intentional?
Shay
