PostgreSQL中如何限制JSONB仅允许string->string键值对

要确保label字段仅为字符串键映射到字符串值的JSON对象，你可以通过以下两种方式实现，无需使用hstore或额外表：

方案一：自定义验证函数 + CHECK约束 #

先创建一个不可变函数，用于验证JSONB是否符合要求：

CREATE OR REPLACE FUNCTION is_string_to_string_jsonb(obj jsonb)
RETURNS boolean AS $$
BEGIN
  -- 首先检查是否为JSON对象
  IF jsonb_typeof(obj) != 'object' THEN
    RETURN false;
  END IF;
  -- 检查所有值的类型是否都是字符串
  RETURN NOT EXISTS (
    SELECT 1
    FROM jsonb_each(obj)
    WHERE jsonb_typeof(value) != 'string'
  );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

然后创建表时添加约束调用该函数：

CREATE TABLE test_labels (
  label JSONB,
  CONSTRAINT valid_string_map CHECK (is_string_to_string_jsonb(label))
);

方案二：直接在CHECK约束中内嵌查询 #

无需自定义函数，直接将验证逻辑写在约束里：

CREATE TABLE test_labels (
  label JSONB,
  CONSTRAINT valid_string_map CHECK (
    jsonb_typeof(label) = 'object'
    AND NOT EXISTS (
      SELECT 1
      FROM jsonb_each(label)
      WHERE jsonb_typeof(value) != 'string'
    )
  )
);

验证效果 #

以上两种方案都会拦截以下不符合要求的输入：

• 数组类型：INSERT INTO test_labels (label) VALUES('["hi", "there"]')
• 单个字符串：INSERT INTO test_labels (label) VALUES('"hi there"')
• 值为非字符串的对象：INSERT INTO test_labels (label) VALUES('{"key": 28}') 或 INSERT INTO test_labels (label) VALUES('{"a": {"b": "c"}}')

可选调整 #

如果不允许插入空对象{}，可以在约束中添加长度检查：

-- 以方案二为例，修改约束如下
CONSTRAINT valid_string_map CHECK (
  jsonb_typeof(label) = 'object'
  AND jsonb_object_length(label) > 0
  AND NOT EXISTS (
    SELECT 1
    FROM jsonb_each(label)
    WHERE jsonb_typeof(value) != 'string'
  )
)

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