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

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;