Search code examples
jsonpostgresqlvalidationvarchar

Postgresql : Is there a way to select all valid json data type

I have a table :

table

---------------------
id  | value
---------------------
1   | invalid_json
---------------------
2   | valid_json
---------------------
3   | invalid_json
---------------------

First of all, value is in varchar type not really declared as json, and it has some reasons why it is set up like that. Anyway, my question is about the possibility, and if possible how. Is it possible to create an sql to find only the rows that contains a VALID json formatted data even though the column data type is var char?

A sort of : 

"select * from table where (condition that data is a valid json)";
Solution

  • As a_horse_with_no_name stated, you can write a function trying to cast to json and return a result based on the success of that operation.

    CREATE FUNCTION is_json(varchar) RETURNS boolean AS $$
  DECLARE
    x json;
  BEGIN
    BEGIN
      x := $1;
    EXCEPTION WHEN others THEN
      RETURN FALSE;
    END;

    RETURN TRUE;
  END;
$$ LANGUAGE plpgsql IMMUTABLE;

    Making it IMMUTABLE will make it operate quickly for repeated strings (such as an empty string for example), but it highly depends on the size of your table.

    Then you can query your data.

    SELECT * FROM table WHERE is_json(value);

    If the table is big and you are about to repeat that query a lot of times, I would add an additional is_json boolean field to the table. Then create a trigger/rule to check the validity upon INSERT/UPDATE on that table.

    However, having mixed data types in the same column is not a good idea, mind changing your data structure in case you are considering such a scenario.