PostgreSQL json testing (`is json`) and casting with null character (`\u0000`) work unexpectedly

Question

I know something about null characters in PostgreSQL text types. But I cannot understand the strange behavior of json testing and casting:

select '{"foo":"bar\u0000"}' is json; --TRUE
select '{"foo":"bar\u0000"}'::json; --JSON
select '{"foo":"bar\u0000"}'::json->'foo'; --ERROR
--ERROR:  unsupported Unicode escape sequence
--DETAIL:  \u0000 cannot be converted to text.
--CONTEXT:  JSON data, line 1: {"foo":"bar\u0000...

Why string with null character is json (in terms of is json construction), casts to json (via ::json), but returns an error on json property access (::json->'foo')?

SELECT version(); --PostgreSQL 16.4 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-17), 64-bit

UPD

I know that this issue has a workaround - replacing null characters from the text before doing json casting and accessing.

But the main problem there as I see it is that this behavior makes is json construction effectively useless when I want to test that some text is in the correct json format. As a user I expect that something correct in terms of is json will lead me to the ability to cast text to json and work with it.

Answer 1

As per the Postgres documentation, the Unicode escape sequence is allowed in json type. However, when you try to extract a json field out of the json type, I believe Postgres tries to convert it to an equivalent type (eg. '{"foo":"bar\u0000"}'::json->'foo' to text) where null byte u/0000 is not allowed in string/text.

You may have to do some workaround to get rid of these codes from your data before storing them in your database. Please refer to this answer.