当记录包含json或字符串的混合时,如何防止Postgres中的"json类型的无效输入语法" [英] How to prevent 'invalid input syntax for type json' in Postgres, when records contain a mix of json or strings
问题描述
我有一个包含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;
这篇关于当记录包含json或字符串的混合时,如何防止Postgres中的"json类型的无效输入语法"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!