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

Question

我有一张 table :

表格

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

首先，value 是 varchar 类型，并没有真正声明为 json，这样设置是有一些原因的。无论如何，我的问题是关于可能性，如果可能的话如何。是否可以创建一个 sql 以仅查找包含 VALID json 格式数据的行，即使列数据类型是 var char？

一种:

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

Answer 1

如 a_horse_with_no_name 所述，您可以编写一个函数尝试转换为 json 并根据该操作的成功返回结果。

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;

将其设置为IMMUTABLE 将使其对重复的字符串(例如空字符串)快速操作，但这在很大程度上取决于您的表的大小。

然后你就可以查询你的数据了。

SELECT * FROM table WHERE is_json(value);

如果表很大并且您要多次重复该查询，我会在表中添加一个额外的 is_json bool 字段。然后创建一个触发器/规则来检查该表上 INSERT/UPDATE 的有效性。

但是，在同一列中混合数据类型并不是一个好主意，如果您正在考虑这种情况，请考虑更改您的数据结构。