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

Question

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)";

Answer 1

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.

Answer 2

I recently solved a similar problem by doing a simple check on the string for curly braces:

WHERE value LIKE '{%}'

This of course depends on the data you expect, and will not match all valid JSON nor exclude all non-JSON. In my case I had a field that used to take a simple character string (still present in old records) but now takes a JSON object wrapped in curly braces. If your case is like mine--you know some specifics about what the valid and invalid data look like--you might do it this way.