Hi,
I am not sure if this is a bug or a known inconvenience. First create a table with a JSON column:
create table test(t json);
insert into test(t) values ('{"foo": "bar"}');
select * from test where t->>'foo' = 'bar';
-- t
-- ----------------
-- {"foo": "bar"}
-- (1 row)
Now, insert a record with a null byte
insert into test(t) values ('{"foo\u0000": "bar"}');
select * from test where t->>'foo' = 'bar';
-- ERROR: unsupported Unicode escape sequence
-- DETAIL: \u0000 cannot be converted to text.
-- CONTEXT: JSON data, line 1: {...
insert into test(t) values ('{"foo\u0000": "bar"}');
select * from test where t->>'foo' = 'bar';
-- ERROR: unsupported Unicode escape sequence
-- DETAIL: \u0000 cannot be converted to text.
-- CONTEXT: JSON data, line 1: {...
Once the null byte is inserted the JSON operator ->> can no longer be applied to the column. JSONB columns don't allow null bytes at all. Should the same constraint apply on JSON? If not, applications must be vigilant to guard against null bytes, or queries could break at read time. My reading of table 8.23 in
https://www.postgresql.org/docs/16/datatype-json.html is they should be disallowed at insert.
