PostgreSQL如何确保json/jsonb列仅插入JSON对象而非数组？

嘿，这个需求我太熟了！要确保PostgreSQL里的json/jsonb列只接受JSON对象（包括空对象{}），拒绝数组或者对象数组的话，用CHECK约束配合json_typeof()函数就搞定了，这是最简洁可靠的方案。

1. 给已存在的表添加约束 #

如果你的users表已经创建好了，直接执行这条SQL给settings列加上约束：

ALTER TABLE users
ADD CONSTRAINT settings_must_be_object
CHECK (json_typeof(settings) = 'object');

不管是json还是jsonb类型，json_typeof()函数都能正常工作，它会返回JSON值的类型：对象返回'object'，数组返回'array'，刚好符合我们的判断需求。

2. 新建表时直接定义约束 #

如果是从零开始建表，直接把约束写在列定义里更方便：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    settings JSONB CHECK (json_typeof(settings) = 'object')
);

3. 测试验证 #

现在来验证你给出的示例场景：

• 合法插入（对象/空对象）：

-- 正常执行，无报错
insert into users (settings) values ('{ "theme": "cobalt" }');
-- 空对象也能正常插入
insert into users (settings) values ('{}');

• 非法插入（数组/对象数组）：

-- 会抛出约束违反错误：new row for relation "users" violates check constraint "settings_must_be_object"
insert into users (settings) values ('[]');
-- 同样会触发约束报错
insert into users (settings) values ('[{}]');

补充：允许NULL值的情况 #

如果你的settings列允许存储NULL值，记得把约束条件调整成下面这样，这样NULL值可以正常插入，同时依然限制非NULL值必须是JSON对象：

CHECK (settings IS NULL OR json_typeof(settings) = 'object')

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