如何向SQLite的JSON数组插入元素且避免重复（类似集合）

现有SQLite表结构如下：

CREATE TABLE "carts" (
    "id"    INTEGER NOT NULL,
    "products"  TEXT NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
)

其中products列存储JSON格式的数字数组文本（例如[12,13,14]），需求是向该字段插入单个元素时自动去重：元素已存在则字段值不变，不存在则追加到数组末尾。

实现方案 #

利用SQLite内置的JSON函数组合实现，核心逻辑是先判断元素是否存在，再决定是否执行追加操作：

更新现有记录

假设要给id=1的购物车添加元素17，执行以下SQL：

UPDATE carts
SET products = CASE
    WHEN JSON_ARRAY_CONTAINS(products, 17) THEN products
    ELSE JSON_INSERT(products, '$[#]', 17)
END
WHERE id = 1;

• JSON_ARRAY_CONTAINS(products, 17)：检查目标数组是否已包含待插入元素
• JSON_INSERT(products, '$[#]', 17)：当元素不存在时，将元素追加到数组末尾（$[#]是SQLite中定位数组最后位置的语法）

验证示例

1. 原数组为[12,13,14]，添加17：因元素不存在，执行追加操作，结果为[12,13,14,17]
2. 原数组为[12,13,14]，添加13：因元素已存在，字段值保持[12,13,14]不变

插入新记录（初始化数组）

如果是创建新购物车并初始化数组，可直接用JSON_ARRAY生成合法的JSON数组文本：

INSERT INTO carts (products)
VALUES (JSON_ARRAY(12));

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