How can I verify in Postgresql that JSON is valid?

Question

I've got a big database with analytics data written in JSON.

I want to filter out rows with incorrect data:

• invalid json (some rows has something like that: '{"hello": "world'
• some attributes is not array so it would take '{"products": [1,2,3]}' and will leave out the '{"products": 1}'

I want to do something like that:

select * 
from analytics 
where (is_correct_json(json::json)) 
and (is_array(json::json->>'products'))

How can I achieve that?

Answer 1

This is another good example why choosing the appropriate data type right from the start helps later ;)

There is no built-in function to check if a given text is valid JSON. You can however write your own:

create or replace function is_valid_json(p_json text)
  returns boolean
as
$$
begin
  return (p_json::json is not null);
exception 
  when others then
     return false;  
end;
$$
language plpgsql
immutable;

Caution: due to the exception handling this is not going to be fast. If you call that on many invalid values this is going to slow down your select massively.

However both '{"products": 1}' and '{"products": [1,2,3]}' are valid JSON documents. The fact that the former is invalid is based on your application logic, not on the JSON syntax.

To verify that you would need a similar function, that traps errors when calling json_array_length()

create or replace function is_valid_json_array(p_json text, p_element text)
  returns boolean
as
$$
begin
  return json_array_length( p_json::json -> p_element) >= 0;
exception 
  when others then
     return false;  
end;
$$
language plpgsql
immutable;