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}'));

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