如何在PostgreSQL的JSON列中阻止插入空字符串/数组并清理空值字段

我来给你几个实用的方案，帮你在PostgreSQL里自动清理JSON列中的空字符串（""）和空数组（[]）字段，确保插入的数据符合预期：

方案1：使用触发器（推荐，自动处理插入/更新） #

这是最省心的方案，通过触发器在数据插入或更新前自动清理无效字段，不需要修改应用层的插入逻辑。

步骤1：创建清理JSON的函数 #

首先定义一个PL/pgSQL函数，负责遍历JSON并过滤掉值为空字符串或空数组的键值对：

CREATE OR REPLACE FUNCTION clean_empty_json_fields()
RETURNS TRIGGER AS $$
BEGIN
  -- 过滤掉值为空字符串或空数组的字段
  NEW.your_json_column = (
    SELECT json_object_agg(key, value)
    FROM json_each(NEW.your_json_column)
    WHERE value NOT IN ('""', '[]')
  );

  -- 可选：如果清理后JSON为空对象，抛出错误阻止插入
  IF NEW.your_json_column = '{}'::json THEN
    RAISE EXCEPTION 'JSON column cannot be empty after removing invalid fields';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：绑定触发器到目标表 #

为你的表创建BEFORE INSERT和BEFORE UPDATE触发器，让函数在数据写入前执行：

-- 处理插入操作的触发器
CREATE TRIGGER trigger_clean_json_before_insert
BEFORE INSERT ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION clean_empty_json_fields();

-- 处理更新操作的触发器（如果需要的话）
CREATE TRIGGER trigger_clean_json_before_update
BEFORE UPDATE ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION clean_empty_json_fields();

注意：把上面的your_json_column替换成你实际的JSON列名，your_table_name替换成目标表名。如果你的列是JSONB类型（推荐使用，性能更优），只需要把json_each换成jsonb_each，json_object_agg换成jsonb_object_agg，同时把'{}'::json改成'{}'::jsonb即可。

方案2：插入时直接用JSON函数处理 #

如果不想用触发器，也可以在INSERT语句中直接处理JSON数据，适合一次性插入或应用层可以控制SQL的场景：

INSERT INTO your_table_name (your_json_column, other_column)
VALUES (
  -- 清理传入的JSON数据
  (
    SELECT json_object_agg(key, value)
    FROM json_each('{"city":"LONDON","country":"UK","addressLine1":"PO Box 223456","postCode":"","addressLine2":"PO Box 47854"}'::json)
    WHERE value NOT IN ('""', '[]')
  ),
  '其他字段的值'
);

执行这条语句后，postCode字段会被自动移除，最终插入的JSON就是你想要的结果。

额外说明 #

• 如果你需要处理嵌套的JSON结构（比如JSON里还有子JSON/数组），可以修改触发器函数，添加递归逻辑来清理嵌套层级的空值字段。
• 测试时可以先在测试环境验证触发器的行为，确保不会影响现有业务数据。

内容的提问来源于stack exchange，提问作者Nik