如何在Postgresql中验证JSON是否有效? [英] How can I verify in Postgresql that JSON is valid?

问题描述

我有一个大型数据库，其中包含以JSON编写的分析数据.

I've got a big database with analytics data written in JSON.

我想过滤出数据不正确的行:

I want to filter out rows with incorrect data:

• 无效的json(某些行的内容如下:'{"hello": "world'
• 某些属性不是数组，因此需要使用'{"products": [1,2,3]}'并忽略了'{"products": 1}'

• invalid json (some rows has something like that: '{"hello": "world'
• some attributes is not array so it would take '{"products": [1,2,3]}' and will leave out the '{"products": 1}'

我想做这样的事情:

select * 
from analytics 
where (is_correct_json(json::json)) 
and (is_array(json::json->>'products'))

我该如何实现?

推荐答案

这是另一个很好的示例，为什么从一开始就选择合适的数据类型会有所帮助；)

This is another good example why choosing the appropriate data type right from the start helps later ;)

没有内置函数来检查给定文本是否为有效JSON.但是，您可以编写自己的:

There is no built-in function to check if a given text is valid JSON. You can however write your own:

create or replace function is_valid_json(p_json text)
  returns boolean
as
$$
begin
  return (p_json::json is not null);
exception 
  when others then
     return false;  
end;
$$
language plpgsql
immutable;

警告:由于异常处理，这不会很快.如果您对许多无效值调用它，将会大大减慢您的选择速度.

Caution: due to the exception handling this is not going to be fast. If you call that on many invalid values this is going to slow down your select massively.

但是'{"products": 1}'和'{"products": [1,2,3]}'都是有效的JSON文档.前者无效的事实是基于您的应用程序逻辑，而不是基于JSON语法.

However both '{"products": 1}' and '{"products": [1,2,3]}' are valid JSON documents. The fact that the former is invalid is based on your application logic, not on the JSON syntax.

要验证您是否需要类似的功能，请在调用json_array_length()

To verify that you would need a similar function, that traps errors when calling json_array_length()

create or replace function is_valid_json_array(p_json text, p_element text)
  returns boolean
as
$$
begin
  return json_array_length( p_json::json -> p_element) >= 0;
exception 
  when others then
     return false;  
end;
$$
language plpgsql
immutable;

这篇关于如何在Postgresql中验证JSON是否有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！