Re: [GENERAL] Removing null bytes from a json column

Timothy Garnett <tgarnett@panjiva.com> writes:
> Does anyone have some tips on how to deal with an existing json type column
> that has some null bytes ( \u0000) in it? It seems like anything I do that
> touches any row with a null byte just errors.  I'd love to just remove them
> if I could find some way to find them, but I'm having trouble even figuring
> out how to do that.

Doesn't it work to cast to text and do a LIKE or regex search?

regression=# select '{"z":"\u0000"}'::json::text ~ '\\u0000';
 ?column?
----------
 t
(1 row)

regression=# select '{"z":"\u0001"}'::json::text ~ '\\u0000';
 ?column?
----------
 f
(1 row)

It's true that we won't let you cast such a value to JSONB or do any
exciting JSON-ish manipulations on it, but I'm not seeing an error
in cast-to-text.

            regards, tom lane