SQLite 文档存储攻略

文章目录

        一个文档存储案例
        JSON1 插件概述
            通用参数说明
        JSON 函数说明
            json()
            json_array()
            json_array_length()
            json_extract()
            json_insert()、json_replace 和 json_set()
            json_object()
            json_patch()
            json_remove()
            json_type()
            json_valid()
            json_quote()
            json_group_array() 和 json_group_object()
            json_each() 和 json_tree()
        编译 JSON1 插件
        版本支持
        总结

SQLite 通过 JSON1 扩展插件提供了许多 JSON 函数，同时 SQLite 表达式索引（Indexes On Expressions）和生成列（Generated Column）为 JSON 数据提供了索引支持，从而实现了文档存储和处理功能。因此，本文给大家详细介绍一下如何将 SQLite 作为一个文档数据库使用。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
一个文档存储案例

我们首先来看一个简单的案例：

SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table docs(
   ...> id int not null primary key,
   ...> content text
   ...> );
sqlite> insert into docs(id, content) values (1, json('{"name":"apple", "price":6.50}'));


首先，我们创建了一个测试表 docs；其中 content 字段用于存储 JSON 文档，字段类型为 TEXT。然后我们使用 json() 函数确保了输入字符串符合 JSON 格式要求，如果参数不满足 JSON 格式要求将会返回错误。例如：

sqlite> select json('"not a valid json string');
Error: malformed JSON



接下来我们可以在查询中使用 JSON 文档中的内容：

sqlite> select * from docs where json_extract(content, '$.name') = 'apple';
1|{"name":"apple","price":6.50}


json_extract() 函数用于从 JSON 文档中返回 name 节点的数据，具体的函数介绍参考下文。

如果想要对以上查询进行优化，可以使用表达式索引。例如：

sqlite> create index docs_name on docs(json_extract(content, '$.name'));

sqlite> explain query plan
   ...> select * from docs where json_extract(content, '$.name') = 'apple';
QUERY PLAN
`--SEARCH TABLE docs USING INDEX docs_name (<expr>=?)



我们对文档 content 中的 name 节点进行了索引，查询时可以通过索引提高检索的速度。

目前还有一个问题，SQLite 并没有提供原始的 JSON 数据类型，content 字段中仍然可以插入任何数据。这个问题我们可以通过生成列来解决。例如：

sqlite> drop table docs;

sqlite> create table docs(
   ...> content text,
   ...> id int generated always as (json_extract(content, '$.id')) virtual not null
   ...> );
sqlite> insert into docs(content) values (json('{"id":1, "name":"apple", "price":6.50}'));



我们将 id 字段定义为一个非空的虚拟生成列，数据来自于 content 字段而不会占用额外的存储。json_extract() 函数的使用意味着插入无效的 JSON 文档同样会返回 Error: malformed JSON 错误信息。例如：

sqlite> insert into docs(content) values (json('{"id":1, "name":"apple", "price":6.50]}'));
Error: malformed JSON

sqlite> insert into docs(content) values (json('{"name":"apple", "price":6.50}'));
Error: NOT NULL constraint failed: docs.id



第一个错误是因为文档不是有效的 JSON 格式，第二个错误是因为文档中没有 id 节点。

由于 SQLite 生成列无法作为主键字段，我们不能将 id 字段定义为该表的主键。不过，我们可以为 id 字段创建一个唯一索引，加上非空约束后的效果和主键一样。

sqlite> create unique index docs_id on docs(id);

sqlite> insert into docs(content) values (json('{"id":1, "name":"banana", "price":8.00}'));
Error: UNIQUE constraint failed: docs.id



接下来我们详细介绍一下 JSON1 插件。
JSON1 插件概述

更多请见：http://www.mark-to-win.com/tutorial/51556.html