JSON_SERIALIZE for JSONB returns parts of the internal JSONB representation
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
Hi,
When running a query like this (tested against PostgreSQL 18.3), it shows internals from the JSONB storage:
postgres=# select JSON_SERIALIZE('[1,2,4]'::jsonb);
json_serialize
----------------
\x03
(1 row)
I think this qualifies as a bug? I would expect JSON_SERIALIZE to either work with JSONB and here output the serialized text, so '[1,2,4]', or to error that JSONB is not supported for this function (or that this function can’t even be found for a JSONB argument).
What happens here is that json_out treats the internal bytes from the JSONB representation as a C string.
From src/include/utils/jsonb.h:
* ... For that purpose, both an array and an object begin with a uint32
* header field, which contains an JB_FOBJECT or JB_FARRAY flag.
This is part of the header:
typedef struct JsonbContainer
{
uint32 header; /* number of elements or key/value pairs, and
* flags */
JEntry children[FLEXIBLE_ARRAY_MEMBER];
/* the data for each child node follows. */
} JsonbContainer;
The rest of the header contains the number of elements, in this case a JSON array of 3 elements. So the total header looks like
uint32(JB_FARRAY | 3) == 0x40000003
So this also explains the \x03 output above. What is seen there is the first byte of the header there as little endian, since that is interpreted as a C string with the a 0 byte that happens to be after there.
--
Cheers,
Dirkjan
Hi Dirkjan,
Thanks for reporting this issue.
select JSON_SERIALIZE('[1,2,4]'::jsonb);
\x03
Reproduced on master. The docs allow any JSON type here, so it should
work. makeJsonConstructorExpr() builds the output coercion for a json
input, because it looks at the RETURNING format, which is always JSON.
The jsonb argument is passed through as is, so json_out() runs on it.
0001 fixes this issue.
0002 adds tests and is optional.
The fix is in the parser, so a view created before it stays wrong until
recreated.
Other options I looked at:
- convert in the executor. Covers old views, but adds a step there and
keeps the wrong coercion in the tree.
- cast jsonb to json in transformJsonSerializeExpr(). Works, converts
twice.
Thanks,
Shihao