Re: Removing null bytes from a json column

Timothy Garnett <tgarnett(at)panjiva(dot)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