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

From: Shay Rojansky <roji(at)roji(dot)org>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: JSON_VALUE() behavior when RETURNING bytea (expected base64 decoding)
Date: 2025-03-06 00:02:44
Message-ID: CADT4RqB9y5A58CAxMgWQpKG2QA1pzk3dzAUmNH8bJ9SwMP=ZnA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ian Lawrence Barwick 2025-03-06 00:10:04 Re: jsonb_strip_nulls with arrays?
Previous Message Fujii Masao 2025-03-05 23:54:59 Re: Disabling vacuum truncate for autovacuum