如何在SQLite CHECK约束中校验JSONArray所有元素类型？

安全阿岳

2026-7-1

SQLite CHECK约束检测JSON数组所有元素的解决方案

问题场景

现有SQLite表document，期望通过CHECK约束确保data字段中JSON数组documentLines的每个元素的title都是文本类型（text），但插入包含title: null的记录时，原约束未触发异常，导致非法数据插入。

原表定义

CREATE TABLE document(
   id TEXT NOT NULL,
   data TEXT NOT NULL,
   CHECK ( json_type(data, '$.documentLines.title') IN ('text') ),
   PRIMARY KEY (id)
) STRICT;

测试插入语句

INSERT INTO document (id, data) VALUES ('1',
'{
    "documentLines": [
        {
            "title": "This is a nice text",
            "lineNumber": "1",
            "content": "bla bla bla"
        },
        {
            "title": "This is a nice text too",
            "lineNumber": "2",
            "content": "bla bla bla"
        },
        {
            "title": null,
            "lineNumber": "3",
            "content": "bla bla bla"
        }
    ]
}');

问题原因

原约束中的json_type(data, '$.documentLines.title')会返回数组中所有title的类型集合（比如'text','null'），当使用IN ('text')判断时，只要集合中存在text类型就会返回true，无法检测到数组中存在的非text类型值。

修改后的CHECK约束

要确保数组中所有title的类型都是text，可以通过json_each遍历数组元素，检查是否存在不符合要求的元素：

CREATE TABLE document(
   id TEXT NOT NULL,
   data TEXT NOT NULL,
   CHECK (
       NOT EXISTS (
           SELECT 1
           FROM json_each(data, '$.documentLines') AS lines
           WHERE json_type(lines.value, '$.title') != 'text'
       )
   ),
   PRIMARY KEY (id)
) STRICT;