在Postgres中防止'json类型的无效输入语法' [英] Preventing 'invalid input syntax for type json' in Postgres

问题描述

我有一个包含JSON和计划文本的文本列.我想将其转换为JSON，然后选择一个特定的属性.例如:

I have a text column that contains JSON and also plan text. I want to convert it to JSON, and then select a particular property. For example:

user_data
_________
{"user": {"name": "jim"}}
{"user": {"name": "sally"}}
some random data string

我尝试过:

select user_data::json#>'{user,name}' from users

我得到:

ERROR:  invalid input syntax for type json
DETAIL:  Token "some" is invalid.
CONTEXT:  JSON user_data, line 1: some...

有可能防止这种情况发生吗?

Is it possible to prevent this?

推荐答案

如果您要跳过包含无效JSON的行，则必须首先测试，以确认文本是否为有效JSON.您可以通过创建一个函数来尝试解析该值，并捕获无效的JSON值的异常来实现此目的.

If you want to skip the rows with invalid JSON, you must first test if the text is valid JSON. You can do this by creating a function which will attempt to parse the value, and catch the exception for invalid JSON values.

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函数来缩小有效值.

When you have that, you could use the is_json function in a CASE or WHERE clause to narrow down the valid values.

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

这篇关于在Postgres中防止'json类型的无效输入语法'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！