Pedro Carvalho Pedro Carvalho - 15 days ago 7
JSON Question

Catch PostgreSQL exception and change column value

I have a column called

metadata
which has format
TEXT
but contains stuff that's usually a JSON. I want to select a certain value in it (
metadata :: JSON -> 'register' ->> 'date'
for instance) but if the JSON is badly formatted or if the field doesn't exist I get an error. Can I make that column just return a
NULL
value when it would get an error instead?

Answer

You can write a stored procedure that tries to cast to json and returns null of failure. Something like this:

create function to_json(t text) returns json as $$
begin
  return t::json;
exception when invalid_text_representation then
  return null;
end;
$$ language plpgsql;

and then use to_json(metadata) -> 'register' ->> 'date'.