How can I verify in Postgresql that JSON is valid? -

i've got big database analytics data written in json.

i want filter out rows incorrect data:

• invalid json (some rows has that: '{"hello": "world'
• some attributes not array take '{"products": [1,2,3]}' , leave out '{"products": 1}'

i want that:

select *  analytics  (is_correct_json(json::json))  , (is_array(json::json->>'products')) 

how can achieve that?

this example why choosing appropriate data type right start helps later ;)

there no built-in function check if given text valid json. can write own:

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

caution: due exception handling not going fast. if call on many invalid values going slow down select massively.

however both '{"products": 1}' , '{"products": [1,2,3]}' valid json documents. fact former invalid based on application logic, not on json syntax.

to verify need similar function, traps errors when calling json_array_length()

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