Handling Unicode sequences in postgresql

\u0000 is the one Unicode code point which is not valid in a string. I see no other way than to sanitize the string.

Since json is just a string in a specific format, you can use the standard string functions, without worrying about the JSON structure. A one-line sanitizer to remove the code point would be:

SELECT (regexp_replace(the_string::text, '\\u0000', '', 'g'))::json;

But you can also insert any character of your liking, which would be useful if the zero code point is used as some form of delimiter.

Note also the subtle difference between what is stored in the database and how it is presented to the user. You can store the code point in a JSON string, but you have to pre-process it to some other character before processing the value as a json data type.

The solution by Patrick didn't work out of the box for me. Regardless there was always an error thrown. I then researched a little more and was able to write a small custom function that fixed the issue for me.

First I could reproduce the error by writing:

select json '{ "a":  "null \u0000 escape" }' ->> 'a' as fails

Then I added a custom function which I used in my query:

CREATE OR REPLACE FUNCTION null_if_invalid_string(json_input JSON, record_id UUID)  RETURNS JSON AS $$DECLARE json_value JSON DEFAULT NULL;BEGIN  BEGIN    json_value := json_input ->> 'location';    EXCEPTION WHEN OTHERS    THEN      RAISE NOTICE 'Invalid json value: "%".  Returning NULL.', record_id;      RETURN NULL;  END;  RETURN json_input;END;$$ LANGUAGE plpgsql;

To call the function do this. You should not receive an error.

select null_if_invalid_string('{ "a":  "null \u0000 escape" }', id) from my_table

Whereas this should return the json as expected:

select null_if_invalid_string('{ "a":  "null" }', id) from my_table

If you don't want those nullbyte results, just add:

AND json NOT LIKE '%\u0000%'

in your WHERE statement