在 Postgres 中防止“类型 json 的无效输入语法”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45971754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Preventing 'invalid input syntax for type json' in Postgres
提问by Allyl Isocyanate
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:
我有一个包含 JSON 和计划文本的文本列。我想将其转换为 JSON，然后选择一个特定的属性。例如：
user_data
_________
{"user": {"name": "jim"}}
{"user": {"name": "sally"}}
some random data string
I've tried:
我试过了：
select user_data::json#>'{user,name}' from users
I get:
我得到：
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?
有没有可能防止这种情况？
采纳答案by Andy Carlson
If you want to skip the rows with invalid JSON, you must first testif 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.
如果要跳过具有无效 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;
When you have that, you could use the is_jsonfunction in a CASEor WHEREclause to narrow down the valid values.
有了它，您可以is_json在CASEorWHERE子句中使用该函数来缩小有效值的范围。
-- 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;
回答by klin
Use this function:
使用这个功能：
create or replace function is_json(text)
returns boolean language plpgsql immutable as $$
begin
perform ::json;
return true;
exception
when invalid_text_representation then
return false;
end $$;
Test:
测试：
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)
