pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedJSON_SERIALIZE for JSONB returns parts of the internal JSONB representation
2+ messages / 2 participants
[nested] [flat]
* JSON_SERIALIZE for JSONB returns parts of the internal JSONB representation
@ 2026-03-03 15:02 Dirkjan Bussink <d.bussink@gmail.com>
2026-09-19 03:19 ` Re: JSON_SERIALIZE for JSONB returns parts of the internal JSONB representation shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dirkjan Bussink @ 2026-03-03 15:02 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: JSON_SERIALIZE for JSONB returns parts of the internal JSONB representation
2026-03-03 15:02 JSON_SERIALIZE for JSONB returns parts of the internal JSONB representation Dirkjan Bussink <d.bussink@gmail.com>
@ 2026-09-19 03:19 ` shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: shihao zhong @ 2026-09-19 03:19 UTC (permalink / raw)
To: Dirkjan Bussink <d.bussink@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
Attachments:
[application/octet-stream] v1-0002-Add-tests-for-JSON_SERIALIZE-with-a-jsonb-argumen.patch (2.1K, ../../CAGRkXqQXwh_gJmQE3PZj0v13omMPgFGBbb8TJoBFH8ECsAFCUg@mail.gmail.com/3-v1-0002-Add-tests-for-JSON_SERIALIZE-with-a-jsonb-argumen.patch)
download | inline diff:
From 17872d6d2a7442c5182e58edb1faa68fd38e2895 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Fri, 18 Sep 2026 23:15:03 -0400
Subject: [PATCH v1 2/2] Add tests for JSON_SERIALIZE() with a jsonb argument
Cover the default RETURNING type, bytea, a varchar that is too short,
and a NULL input.
---
src/test/regress/expected/sqljson.out | 21 +++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 6 ++++++
2 files changed, 27 insertions(+)
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 30f9e10ef0a..12353d68686 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -270,6 +270,27 @@ SELECT pg_typeof(JSON_SERIALIZE(NULL));
text
(1 row)
+-- jsonb input
+SELECT JSON_SERIALIZE('{"b": [1, 2], "a": "x"}'::jsonb);
+ json_serialize
+-------------------------
+ {"a": "x", "b": [1, 2]}
+(1 row)
+
+SELECT JSON_SERIALIZE('[1, 2, 4]'::jsonb RETURNING bytea);
+ json_serialize
+----------------------
+ \x5b312c20322c20345d
+(1 row)
+
+SELECT JSON_SERIALIZE('[1, 2, 4]'::jsonb RETURNING varchar(3));
+ERROR: value too long for type character varying(3)
+SELECT JSON_SERIALIZE(NULL::jsonb);
+ json_serialize
+----------------
+
+(1 row)
+
-- only string types or bytea allowed
SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb);
ERROR: cannot use type jsonb in RETURNING clause of JSON_SERIALIZE()
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 9fa203ccb53..43ef31b7e8b 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -55,6 +55,12 @@ SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING bytea);
SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar);
SELECT pg_typeof(JSON_SERIALIZE(NULL));
+-- jsonb input
+SELECT JSON_SERIALIZE('{"b": [1, 2], "a": "x"}'::jsonb);
+SELECT JSON_SERIALIZE('[1, 2, 4]'::jsonb RETURNING bytea);
+SELECT JSON_SERIALIZE('[1, 2, 4]'::jsonb RETURNING varchar(3));
+SELECT JSON_SERIALIZE(NULL::jsonb);
+
-- only string types or bytea allowed
SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb);
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v1-0001-Fix-JSON_SERIALIZE-with-a-jsonb-argument.patch (1.8K, ../../CAGRkXqQXwh_gJmQE3PZj0v13omMPgFGBbb8TJoBFH8ECsAFCUg@mail.gmail.com/4-v1-0001-Fix-JSON_SERIALIZE-with-a-jsonb-argument.patch)
download | inline diff:
From eb827c81f4eec723bf1b46a6036309f2e89413d0 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Fri, 18 Sep 2026 23:14:30 -0400
Subject: [PATCH v1 1/2] Fix JSON_SERIALIZE() with a jsonb argument
JSON_SERIALIZE() returns its argument as is and then applies the
coercion to the RETURNING type. makeJsonConstructorExpr() built that
coercion for a json input, because it picked the input type from the
RETURNING format, which is always JSON here. A jsonb argument is not
converted before that, so json_out() ran on jsonb data. The result
was a few bytes of the jsonb header, which could also be text that is
not valid in the server encoding.
Build the coercion from the type of the argument instead.
This is fixed in the parser, so views and other stored expressions
that call JSON_SERIALIZE() on a jsonb value need to be recreated.
Reported-by: Dirkjan Bussink <d.bussink@gmail.com>
Discussion: https://postgr.es/m/03583FE7-A5EE-407C-907D-67865F3F8BFC@gmail.com
Backpatch-through: 17
---
src/backend/parser/parse_expr.c | 12 ++++++++++--
1 file changed, 10 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 05a6b72d4c9..6baeb1d5b06 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3710,8 +3710,16 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type,
{
CaseTestExpr *cte = makeNode(CaseTestExpr);
- cte->typeId = returning->format->format_type == JS_FORMAT_JSONB ?
- JSONBOID : JSONOID;
+ if (type == JSCTOR_JSON_SERIALIZE)
+ cte->typeId = exprType(linitial(args));
+ else
+ cte->typeId = returning->format->format_type == JS_FORMAT_JSONB ?
+ JSONBOID : JSONOID;
cte->typeMod = -1;
cte->collation = InvalidOid;
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-19 03:19 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-03 15:02 JSON_SERIALIZE for JSONB returns parts of the internal JSONB representation Dirkjan Bussink <d.bussink@gmail.com>
2026-09-19 03:19 ` shihao zhong <zhong950419@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox