如果你想跳过包含无效JSON的行，你必须首先测试文本是否是有效的JSON。你可以通过创建一个函数来尝试解析值，并捕获无效JSON值的异常来实现这一点。

CREATE OR REPLACE FUNCTION is_json(input_text varchar) RETURNS boolean AS $$
  DECLARE
    maybe_json json;
  BEGIN
    BEGIN
      maybe_json := input_text;
    EXCEPTION WHEN others THEN
      RETURN FALSE;
    END;

    RETURN TRUE;
  END;
$$ LANGUAGE plpgsql IMMUTABLE;

字符串
当你有了它，你可以在CASE或WHERE子句中使用is_json函数来缩小有效值的范围。

-- this can eliminate invalid values
SELECT user_data::json #> '{user,name}'
FROM users WHERE is_json(user_data);

-- or this if you want to fill will NULLs
SELECT
  CASE
    WHEN is_json(user_data)
      THEN user_data::json #> '{user,name}'
    ELSE
      NULL
  END
FROM users;

型

使用此功能：

create or replace function is_json(text)
returns boolean language plpgsql immutable as $$
begin
    perform $1::json;
    return true;
exception
    when invalid_text_representation then 
        return false;
end $$;

字符串
试验项目：

with users(user_data) as (
values
    ('{"user": {"name": "jim"}}'),
    ('not json'),
    ('{"user": {"name": "sally"}}'),
    ('also not json')
)

select user_data::json#>'{user,name}' as name
from users
where is_json(user_data);

  name   
---------
 "jim"
 "sally"
(2 rows)

型

其他人已经建议了检查JSON是否有效的方法，但我觉得与其检查，为什么不直接转换它呢？我使用这个函数（JSONB，但你可以很容易地将其更改为JSON）：

CREATE OR REPLACE FUNCTION safe_cast_to_jsonb(input TEXT) RETURNS JSONB AS
$$
DECLARE
    output JSONB DEFAULT NULL;
BEGIN
    BEGIN
        output := input::JSONB;
    EXCEPTION
        WHEN OTHERS THEN RAISE NOTICE 'INVALID JSONB';
        RETURN NULL;
    END;
    RETURN output;
END;
$$ LANGUAGE plpgsql;

字符串

我的解决办法是做一个预选来解决这个问题。
假设我们得到了一个给定的表users，其中列id和user_data。user_data被声明为文本字段，它通常应该包含{firstName, lastName}。

• 请注意，如果给定字段为 NULL，则Postgres JSON操作可以正常工作-因此您可以在以下语句中省略 WHERE，以便仍然选择包含user_data中无效JSON的行（而结果集将仅包含此类行中的id字段）
• user_data ~ '^{.*}$'（“花括号检查”）检查user_data字段中是否包含JSON。**危险！**请记住，此检查是不完整的。错误的JSON，如“{“key 1”：“value 1”，“key 2”}“仍然被识别为有效
• 考虑对数据库性能的影响

作为一个例子：

WITH users_json_data AS (
  SELECT 
    id,
    CASE WHEN user_data ~ '^{.*}$' THEN user_data::json END AS user_json
  FROM users
)
SELECT id, users_json_data.user_json->>firstName, users_json_data.user_json->>lastName
FROM users
INNER JOIN users_json_data USING (id)
WHERE users_json_data.user_json IS NOT NULL

字符串