本文需要 SQLite >= 3.31.0(2020-01-22)

在 SQLite 的新版本中已经支持了 json 函数 (json_extract)，我们完全可以把它当做一个文档 数据库使用。而且更妙的是，还可以直接添加"合成列"(generated column), 也就是使用 json 中的 字段作为虚拟的列。最牛逼的是，这些"生成列"上还可以加索引，包括 unique 索引。

之前用 MongoDB 的时候，最爽的地方就是可以随便往里面插入东西，不用纠结先创建表；最不爽的 就是加索引的语法太难记了，而且好像得先把表创建出来。另一方面，SQL 的 select 语法已经像 思想钢印一样打进了每个程序员的大脑，使用 MongoDB 的查询语法多少有些不适应。而从刚刚提到 的 sqlite 的新功能来看，sqlite 甚至比 MongoDB 用起来还能更爽一些。可以按照以下几步操作：

我们可以预先不定义表的具体结构，只留一个 body 字段：create table my_data(body text);, 有什么数据，先直接往里面插入就好了。

需要插入数据的时候，可以直接插入 json:

sqlite> insert into my_data (body) values ('{"id": 1, "text": "foo"}');
sqlite> insert into my_data (body) values ('{"id": 2, "text": "bar"}');

需要一些 ad hoc 的查询的时候，可以使用 json_extract 函数直接查询：

select json_extract(body, '$.id') from my_data;

或者可以创建一个"合成列"(generate column), 这样就可以查询了：

alter table my_data add column id int generated always as (json_extract(body, '$.id')) virtual;
select * from data where id = 2;

当然也可以在创建表的时候就加上需要的列

create table my_data(
    body text,
    id int generated always as (json_extract(body, '$.id')) virtual
);

为了个给查询加速，我们可以创建索引，合成列支持索引

create index idx_id on my_data(id);
explain query plan select id from my_data where id=1;
QUERY PLAN
--SEARCH TABLE my_data USING INDEX idx_id (id=?)

我们可以看到，查询时确实使用了索引。

如果我们在数据插入过程中发现有一些重复数据，那很简单，直接把索引改成 unique 所有就好了， 就像普通的 sql 数据库一样。

create unique index uniq_id on my_data(id);

如何验证插入的 json 是否合法呢？其实刚刚我们已经加上了，generated always 表示的是总是 生成这个字段，既然能抽取这个字段了，那么这个 json 显然是合法的。当插入一个非法的 json 的 时候就会报错。

如果要保证某个字段一定存在，那么还是用 SQL 的语法，加上一个 not null 就可以了。

sqlite> insert into x values('');
Error: malformed JSON
sqlite> insert into x values('{}');
Error: NOT NULL constraint failed: my_data.id

现在，我们来看一下 virtual 关键字。上面的 virtual 表示这个字段总是查询的时候抽取，对 应的另一种模式叫做 stored, 也就是缓存下这个字段。使用 stored 的好处是每次避免的了 json 解析的消耗，但是缺点是只能在 create table 的时候添加 stored 字段，不能使用 alter table 添加。实际上 virtual 是默认模式，也就是我们可以省略 virtual.

以上就是主要内容了，再提一些小技巧：

json 函数验证字符串是否是合法的 json, 并且返回一个压缩过后（也就是去除了无用的空格换行） 的 json. 所以可以在插入的时候使用 json 函数过滤一下文档。

参考

1. https://dgl.cx/2020/06/sqlite-json-support
2. https://www.sqlite.org/gencol.html
3. https://www.sqlite.org/json1.html