SQLite 文档存储攻略
文章目录
大家好，我是只谈技术不剪发的 Tony 老师。
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 插件概述
json1 插件是一个可加载的扩展，实现了 15 个应用程序定义的 SQL 函数和 2 个表值函数，可以用于管理 SQLite 中的 JSON 文档。其中，以下 13 个函数是标量函数：
- json(json)，验证输入参数是否符合 JSON 格式并返回结果。
- json_array(value1,value2,…)，创建一个 JSON 数组。
- json_array_length(json)，返回 JSON 数组中的元素个数。
- json_array_length(json,path)，返回指定路径上的 JSON 数组中的元素个数。
- json_extract(json,path,…)，提取指定路径上的元素。
- json_insert(json,path,value,…)，在指定路径上插入元素。
- json_object(label1,value1,…)，创建一个 JSON 对象。
- json_patch(json1,json2)，增加、修改或者删除 JSON 对象中的元素。
- json_remove(json,path,…)，删除指定路径上的元素。
- json_replace(json,path,value,…)，替换指定路径上的元素。
- json_set(json,path,value,…)，设置指定路径上的元素。
- json_type(json)，返回最外层元素的 JSON 数据类型。
- json_type(json,path)，返回指定路径上的元素的 JSON 数据类型。
- json_valid(json)，验证输入参数是否符合 JSON 格式。
- json_quote(value)，将 SQL 数据转换为 JSON 格式。
以下 2 个是聚合函数：
- json_group_array(value)，返回聚合后的 JSON 数组。
- json_group_object(name,value)，返回聚合后的 JSON 对象。
以下 2 个是表值函数：
- json_each(json) 和 json_each(json,path)，将 JSON 元素转换为 SQL 数据行。
- json_tree(json) 和 json_tree(json,path)，递归遍历 JSON 元素并转换为 SQL 数据行。
json1 插件目前使用文本存储 JSON 数据。向下兼容意味着 SQLite 只能存储 NULL、整数、浮点数、文本以及 BLOB，无法增加第 6 个类型“JSON”。
json1 插件目前不支持 JSON 文档的二进制编码（BSON）。经过试验没有找到比纯文本编码格式明显更小或者更快的二进制编码，目前的实现可以支持 1GB/s 的 JSON 文本解析。所有的 json1 函数参数都不接受 BLOB，如果指定这种参数将会报错，因为 BLOB 是为了将来增强而保留的二进制 JSON 存储类型。
json1 扩展名中的数字“1”是故意设计的，设计人员预计将来会基于 json1 的经验创建新的不兼容的 JSON 扩展。一旦获得足够的经验，某种JSON 扩展可能会被添加到 SQLite 核心代码中。目前，对 JSON 的支持仍然是通过扩展的形式实现。
通用参数说明
对于第一个参数是 JSON 的函数，该参数可以是一个 JSON 对象、数组、数字、字符串或者 null。SQLite 数字和 NULL 值分别被当作 JSON 数字和 null，SQLite 文本可以被当作 JSON 对象、数组或者字符串。如果 SQLite 本文不符合 JSON 对象、数组或者字符串格式，函数将会返回错误， json_valid() 和 json_quote() 函数除外。
为了验证格式的有效性，JSON 输入参数中开头和结尾的空白字符将会被忽略。根据 JSON 规范，内部的空白字符也会被忽略。这些函数完全遵循 RFC-7159 JSON 语法。
对于接受 PATH 参数的函数，PATH 必须满足一定的格式，否则函数将会返回错误。满足格式的 PATH 必须是一个以“$”符号开头，加上零个或多个“.objectlabel”或者“[arrayindex]”组成的文本。
其中，arrayindex 通常是一个非负的整数 N，表示选择数组的第 N 个元素，从 0 开始计数。arrayindex 也可以使用“#-N”的形式，表示选择从右边开始的第 N 个元素。数组最后一个元素是“#-1”，字符“#”相当于数据元素的个数。
对于接受 value 参数（value1，value2 等）的函数，这些参数通常被当作引号引用的字符串常量，并且最终解析为 JSON 字符串数据。不过，如果某个 value 参数直接来自另一个 json1 函数的输出结果，那么该参数将被当作实际的 JSON，传入的将会是完整的 JSON 而不是字符串常量。
例如，在下面的 json_object() 函数调用中，value 参数看起来像是一个满足格式的 JSON 数组。但是，由于它是一个普通的 SQL 文本，因此被解析为一个字符串常量，并且作为一个字符串被添加到结果中：
SELECT json_object('ex','[52,3.14159]');
json_object('ex','[52,3.14159]')|
--------------------------------|
{"ex":"[52,3.14159]"} |
但是，如果一个外部 json_object() 调用中的 value 参数来自另一个函数的结果，例如 json() 或者 json_array()，将会被解析为实际的 JSON 并且作为 JSON 添加到结果中：
SELECT json_object('ex',json('[52,3.14159]'));
json_object('ex',json('[52,3.14159]'))|
--------------------------------------|
{"ex":[52,3.14159]} |
SELECT json_object('ex',json_array(52,3.14159));
json_object('ex',json_array(52,3.14159))|
----------------------------------------|
{"ex":[52,3.14159]} |
总之，json 参数总是被解释为 JSON，无论该参数的值来自何处。但是 value 参数只有当其直接来自另一个 json1 函数时才被解释为 JSON。
JSON 函数说明
接下来我们详细介绍 json1 扩展中的各种函数。
json()
json(X) 函数可以验证参数 X 符合 JSON 字符串的格式，并且返回一个精简版的 JSON 字符串（删除了所有不必要的空白字符）。如果 X 不是一个格式正确的 JSON 字符串，函数将会返回错误。
如果参数 X 是一个包含重复标签的 JSON 对象，不确定是否保留重复元素。当前实现保留了重复元素，但是将来可能会删除重复元素，而且没有提示。例如：
SELECT json(' { "this" : "is", "a": [ "test" ] } ') AS doc;
doc |
--------------------------|
{"this":"is","a":["test"]}|
SELECT json(' { "this" : "is", "a": [ "test" } ') AS doc;
SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (malformed JSON)
json_array()
json_array(value1,value2,…) 函数接收零个或多个参数，并且返回一个由这些参数组成的 JSON 数组。如果任何参数是 BLOB，函数将会返回错误。
TEXT 类型的参数通常会转换为引号包含的 JSON 字符串。但是，如果该参数来自其他 json1 函数的输出，将会作为 JSON 传入。这种处理方式可以实现 json_array() 和 json_object() 函数的嵌套调用。json() 函数也可以将字符串转换为 JSON。
例如：
SELECT json_array(1,2,'3',4) AS doc;
doc |
-----------|
[1,2,"3",4]|
SELECT json_array('[1,2]') AS doc;
doc |
---------|
["[1,2]"]|
SELECT json_array(json_array(1,2)) AS doc;
doc |
-------|
[[1,2]]|
SELECT json_array(1,null,'3','[4,5]','{"six":7.7}') AS doc;
doc |
------------------------------------|
[1,null,"3","[4,5]","{\"six\":7.7}"]|
SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) AS doc;
doc |
------------------------------|
[1,null,"3",[4,5],{"six":7.7}]|
json_array_length()
json_array_length(X) 函数返回 JSON 数组 X 中的元素个数，如果 X 是其他 JSON 数据而不是数组时返回 0。json_array_length(X,P) 函数返回路径 P 对应数组中的元素个数，如果 X 或者路径 P 对应的是其他 JSON 数据而不是数组时返回 0，如果路径 P 没有对应的元素时返回 NULL。如果 X 不是一个格式正确的 JSON 字符串，或者 P 不是一个格式正确的路径，函数将会返回错误。
例如：
SELECT json_array_length('[1,2,3,4]') AS length;
length|
------|
4|
SELECT json_array_length('[1,2,3,4]', '$') AS length;
length|
------|
4|
SELECT json_array_length('[1,2,3,4]', '$[2]') AS length;
length|
------|
0|
SELECT json_array_length('{"one":[1,2,3]}') AS length;
length|
------|
0|
SELECT json_array_length('{"one":[1,2,3]}', '$.one') AS length;
SELECT json_array_length('{"one":[1,2,3]}', '$.two') AS length;
length|
------|
|
json_extract()
json_extract(X,P1,P2,…) 函数提取并返回 JSON 数据 X 中的一个或多个元素。如果只提供了路径 P1，对于 JSON null 返回的数据类型为 NULL，对于 JSON 数字返回的数据类型为 INTEGER 或者 REAL，对于 JSON false 返回的数据为 INTEGER 类型的 0，对于 JSON true 返回的数据为 INTEGER 类型的 1，对于 JSON 字符串返回的数据类型为去掉引号的文本，对于 JSON 对象和数组返回的是它们的文本形式。如果指定了多个路径参数（P1、P2 等），函数将会返回 SQLite 文本形式的 JSON 数组，包含了每个路径对应的数据。
例如：
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') AS doc;
doc |
-------------------------|
{"a":2,"c":[4,5,{"f":7}]}|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') AS doc;
doc |
-------------|
[4,5,{"f":7}]|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') AS doc;
doc |
-------|
{"f":7}|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') AS doc;
doc|
---|
7|
SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') AS doc;
doc |
---------|
[[4,5],2]|
SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') AS doc;
doc|
---|
5|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') AS doc;
doc|
---|
|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') AS doc;
doc |
--------|
[null,2]|
json_insert()、json_replace 和 json_set()
json_insert(json,path,value,…)、json_replace(json,path,value,…) 和 json_set(json,path,value,…) 函数的第一个参数是一个 JSON 数据，加上零个或多个路径和数据的参数对，使用 path/value 参数对更新输入的 JSON 数据后返回一个新的 JSON 字符串。这些函数的区别仅仅在于创建新值和覆盖旧值得方式不同。
| 函数 | 是否覆盖已有元素 | 是否创建不存在的元素 |
|---|---|---|
| json_insert() | ❌ | ✔️ |
| json_replace() | ✔️ | ❌ |
| json_set() | ✔️ | ✔️ |
这三个函数参数的个数总是奇数，第一个参数总是需要修改的原始 JSON。随后的参数成对出现，每对参数中的第一个是路径，第二个是在该路径上插入、替换或者设置的数据。
数据的修改按照从左至右的顺序执行，前面的数据更改会影响后续的路径搜索。
如果某个 path/value 参数对中的数据是 TEXT 类型，通常来说将会作为一个引号引用的 JSON 字符串插入，即使这个字符串看起来像有效的 JSON。不过，如果该数据值另一个 json1 函数（例如 json()、json_array() 或者 json_object()）的结果，将被解释为一个 JSON 插入并且保留所有的子结构。
如果第一个参数不是一个格式正确的 JSON，或者任何 PATH 不是一个格式正确的路径，或者任何参数是 BLOB，函数将会返回错误。
如果想要在数据的最后追加元素，可以使用 json_insert() 函数并且指定索引下标“#”。例如：
SELECT json_insert('[1,2,3,4]','$[#]',99) AS doc;
doc |
------------|
[1,2,3,4,99]|
SELECT json_insert('[1,[2,3],4]','$[1][#]',99) AS doc;
doc |
--------------|
[1,[2,3,99],4]|
其他示例：
SELECT json_insert('{"a":2,"c":4}', '$.a', 99) AS doc;
doc |
-------------|
{"a":2,"c":4}|
SELECT json_insert('{"a":2,"c":4}', '$.e', 99) AS doc;
doc |
--------------------|
{"a":2,"c":4,"e":99}|
SELECT json_replace('{"a":2,"c":4}', '$.a', 99) AS doc;
doc |
--------------|
{"a":99,"c":4}|
SELECT json_replace('{"a":2,"c":4}', '$.e', 99) AS doc;
doc |
-------------|
{"a":2,"c":4}|
SELECT json_set('{"a":2,"c":4}', '$.a', 99) AS doc;
doc |
--------------|
{"a":99,"c":4}|
SELECT json_set('{"a":2,"c":4}', '$.e', 99) AS doc;
doc |
--------------------|
{"a":2,"c":4,"e":99}|
SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]') AS doc;
doc |
---------------------|
{"a":2,"c":"[97,96]"}|
SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) AS doc;
doc |
-------------------|
{"a":2,"c":[97,96]}|
SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) AS doc;
doc |
-------------------|
{"a":2,"c":[97,96]}|
json_object()
json_object(label1,value1,…) 函数接收零个或多个参数对，并且返回一个由这些参数组成的 JSON 对象。。每对参数中的第一个是元素标签，第二个是对应的数据。如果任何参数是 BLOB，函数将会返回错误。
json_object() 函数目前可以接受重复的元素标签，将来可能不允许。
如果只传入一个 TEXT 类型的参数，即使它是一个格式正确的 JSON，通常也会被转换为引号引用的 JSON 字符串。不过，如果该参数直接来自其他 json1 函数的输出，将被被当作 JSON 处理，所有的类型信息和子结构都会保留。这种处理方式可以实现 json_array() 和 json_object() 函数的嵌套调用。json() 函数也可以将字符串转换为 JSON。
例如：
SELECT json_object('a',2,'c',4) AS doc;
doc |
-------------|
{"a":2,"c":4}|
SELECT json_object('a',2,'c','{e:5}') AS doc;
doc |
-------------------|
{"a":2,"c":"{e:5}"}|
SELECT json_object('a',2,'c',json_object('e',5)) AS doc;
doc |
-------------------|
{"a":2,"c":{"e":5}}|
json_patch()
json_patch(T,P) 函数利用 RFC-7396 MergePatch 算法将补丁 P 应用到输入 T，返回修补之后的 T 副本。
MergePatch 可以增加、修改或者删除 JSON 对象中的元素，因此对于 JSON 对象，json_patch() 函数一般可以作为 json_set() 和 json_remove() 函数的替代。不过，MergePatch 将 JSON 数组当作原子对象处理，不能追加或者修改数组中的单个元素，只能将整个数组作为一个单元进行插入、替换或者删除。因此，json_patch() 对于处理包含数组（尤其是数组中包含很多子结构）的 JSON 用处不大。
例如：
SELECT json_patch('{"a":1,"b":2}','{"c":3,"d":4}') AS doc;
doc |
-------------------------|
{"a":1,"b":2,"c":3,"d":4}|
SELECT json_patch('{"a":[1,2],"b":2}','{"a":9}') AS doc;
doc |
-------------|
{"a":9,"b":2}|
SELECT json_patch('{"a":[1,2],"b":2}','{"a":null}') AS doc;
doc |
-------|
{"b":2}|
SELECT json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') AS doc;
doc |
-------------|
{"a":9,"c":8}|
SELECT json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') AS doc;
doc |
-------------------------------|
{"a":{"x":1,"y":9},"b":3,"c":8}|
json_remove()
json_remove(X,P,…) 函数第一个参数 X 是一个 JSON 数据，加上零个或多个路径参数 P，返回一个删除指定元素后的 JSON。如果指定路径上没有对应的元素，忽略该参数。
数据的删除按照从左至右的顺序执行，前面的数据更改会影响后续的路径搜索。
如果没有指定路径参数，json_remove(X) 函数将会返回格式化后的 X，删除了多余的空白字符。
如果第一个参数不是一个格式正确的 JSON，或者任何 PATH 不是一个格式正确的路径，或者任何参数是 BLOB，函数将会返回错误。
例如：
SELECT json_remove('[0,1,2,3,4]','$[2]') AS doc;
doc |
---------|
[0,1,3,4]|
SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]') AS doc;
doc |
-------|
[1,3,4]|
SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]') AS doc;
doc |
-------|
[1,2,4]|
SELECT json_remove('[0,1,2,3,4]','$[#-1]','$[0]') AS doc;
doc |
-------|
[1,2,3]|
SELECT json_remove('{"x":25,"y":42}') AS doc;
doc |
---------------|
{"x":25,"y":42}|
SELECT json_remove('{"x":25,"y":42}','$.z') AS doc;
doc |
---------------|
{"x":25,"y":42}|
SELECT json_remove('{"x":25,"y":42}','$.y') AS doc;
doc |
--------|
{"x":25}|
SELECT json_remove('{"x":25,"y":42}','$') AS doc;
doc|
---|
|
json_type()
json_type(X) 函数返回 X 最外层元素的 JSON 数据类型。json_type(X,P) 函数返回路径 P 对应元素的 JSON 数据类型。json_type() 函数返回的结果为以下字符串之一：‘null’、‘true’、‘false’、‘integer’、‘real’、‘text’、‘array’ 或者 ‘object’。如果 json_type(X,P) 函数中的路径 P 对应的元素不存在，函数将会返回 NULL。
如果参数不是一个格式正确的 JSON，或者参数是 BLOB，函数将会返回错误。
例如：
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}') AS type;
type |
------|
object|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$') AS type;
type |
------|
object|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a') AS type;
type |
-----|
array|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]') AS type;
type |
-------|
integer|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]') AS type;
type|
----|
real|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]') AS type;
type|
----|
true|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]') AS type;
type |
-----|
false|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]') AS type;
type|
----|
null|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]') AS type;
type|
----|
text|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]') AS type;
type|
----|
|
json_valid()
json_valid(X) 函数用于验证参数的格式。如果 X 是一个格式正确的 JSON，函数返回 1；否则，函数返回 0。
例如：
SELECT json_valid('{"x":35}') AS is_json;
is_json|
-------|
1|
SELECT json_valid('{"x":35') AS is_json;
is_json|
-------|
0|
json_quote()
json_quote(X) 函数将 SQL 数据 X（一个数字或者字符串）转换为对应的 JSON 形式。例如：
SELECT json_quote(3.14159) AS json;
json |
-------|
3.14159|
SELECT json_quote('verdant') AS json;
json |
---------|
"verdant"|
json_group_array() 和 json_group_object()
json_group_array(X) 函数是一个聚合函数，返回一个由所有 X 构成的 JSON 数组。例如：
SELECT json_group_array(X)
FROM (
SELECT json_array(1,2) AS X
UNION ALL
SELECT json_array(3,4)
UNION ALL
SELECT 5
) t;
json_group_array(X)|
-------------------|
[[1,2],[3,4],5] |
与此类似，json_group_object(NAME,VALUE) 也是一个聚合函数，返回一个由所有 NAME/VALUE 对组成的 JSON 对象。例如：
SELECT json_group_object(name, value)
FROM (
SELECT 'first' AS name, json_object('a',2,'c',4) AS value
UNION ALL
SELECT 'rgb', json_array(255,255,255)
UNION ALL
SELECT 'id', 100
) t;
json_group_object(name, value) |
----------------------------------------------------|
{"first":{"a":2,"c":4},"rgb":[255,255,255],"id":100}|
json_each() 和 json_tree()
json_each(X) 和 json_tree(X) 表值函数将输入参数 X 中的每个元素转换为一行数据。json_each(X) 函数只遍历顶层 JSON 数组或者对象的直接子节点，如果顶层元素是一个基本值则只返回该节点自身。json_tree(X) 函数从顶层元素开始递归遍历所有的 JSON 子结构。
json_each(X,P) 和 json_tree(X,P) 函数和上面两个函数类似，只是它们将路径 P 对应的元素作为顶层元素。
json_each() 和 json_tree() 函数返回的表结构如下：
CREATE TABLE json_tree(
key ANY, -- key for current element relative to its parent
value ANY, -- value for the current element
type TEXT, -- 'object','array','string','integer', etc.
atom ANY, -- value for primitive types, null for array & object
id INTEGER, -- integer ID for this element
parent INTEGER, -- integer ID for the parent of this element
fullkey TEXT, -- full path describing the current element
path TEXT, -- path to the container of the current row
json JSON HIDDEN, -- 1st input parameter: the raw JSON
root TEXT HIDDEN -- 2nd input parameter: the PATH at which to start
);
- 字段 key 是 JSON 数组中每个元素的下标，或者 JSON 对象中每个元素的标签。其他情况下，key 字段为空。
- 字段 atom 是基本元素（除了 JSON 数组和对象之外的元素）对应的 SQL 值，JSON 数组和对象的 atom 字段为空。对于基本 JSON 元素而言，字段 value 的值和 atom 字段相同；对于 JSON 数组和对象元素而言，字段 value 是文本格式的 JSON 数据。
- 字段 type 的值是一个 SQL 文本，根据 JSON 元素的类型不同可能的取值为 ‘null’、‘true’、‘false’、‘integer’、‘real’、‘text’、‘array’ 或者 ‘object’。
- 字段 id 是一个整数，标识了 JSON 字符串中的每个的 JSON 元素。id 是一个内部生成的编号，计算方法在将来的版本中可能会发生改变。唯一可以确认的是每一行都会有一个不同的编号。
- 字段 parent 对于 json_each() 函数总是返回 NULL。对于 json_tree() 函数，字段 parent 是当前元素的父节点 id；如果是顶层元素，字段的值为 NULL。
- 字段 fullkey 是一个文本值，标识了当前元素在原始 JSON 字符串中的路径。即使通过参数 root 提供了其他的起点，也会返回从真正的顶层元素开始的完整路径。
- 字段 path 是到包含当前行的数组或对象容器的路径，或者顶层元素是一个基本类型时（意味着函数只返回当前行）到当前行的路径。
json_each() 示例
假设存在以下 user 表：
CREATE TABLE user(name, phone);
INSERT INTO user(name, phone) VALUES ('anne', json_array('010-12345678', '020-10003333'));
INSERT INTO user(name, phone) VALUES ('tony', json_array('010-12349999', '800-10007777'));
字段 phone 中使用 JSON 数组的形式存储了零个或多个电话号码。以下语句可以找出电话号码以 020 开头的用户：
SELECT DISTINCT user.name
FROM user, json_each(user.phone)
WHERE json_each.value LIKE '010-%';
name|
----|
anne|
现在假设当用户只有一个电话号码时，字段 phone 中存储的是普通文本。例如：
INSERT INTO user(name, phone) VALUES ('kevin', '020-10005555');
现在同样需要找出电话号码以 020 开头的用户。由于 json_each() 函数要求第一个参数是一个格式正确的 JSON，因此它只能用于包含 2 个或更多电话号码的用户：
SELECT name FROM user WHERE phone LIKE '020-%'
UNION ALL
SELECT user.name
FROM user, json_each(user.phone)
WHERE json_valid(user.phone)
AND json_each.value LIKE '020-%';
name |
-----|
anne|
kevin|
json_tree() 示例
假设存在以下 big 表：
CREATE TABLE big(json JSON);
INSERT INTO big(json) VALUES (json_object('name', 'anne','phone', json_array('010-12345678', '020-10003333')));
INSERT INTO big(json) VALUES (json_object('name', 'tony','phone', json_array('010-12349999', '800-10007777')));
如果想要逐行返回数据中的内容，可以执行以下语句：
SELECT big.rowid, fullkey, value
FROM big, json_tree(big.json)
WHERE json_tree.type NOT IN ('object','array');
rowid|fullkey |value |
-----|----------|------------|
1|$.name |anne |
1|$.phone[0]|010-12345678|
1|$.phone[1]|020-10003333|
2|$.name |tony |
2|$.phone[0]|010-12349999|
2|$.phone[1]|800-10007777|
查询条件中的 type NOT IN (‘object’,‘array’) 从结果中去除了容器节点，只返回了叶子元素。我们也可以使用以下语句实现相同的效果：
SELECT big.rowid, fullkey, atom
FROM big, json_tree(big.json)
WHERE atom IS NOT NULL;
假设 big.json 字段中的每一行是一个 JSON 对象，包含一个唯一标识节点’$.id’ 和一个嵌套其他对象的 ‘$.partlist’ 节点。例如：
INSERT INTO big(json) VALUES (json_object('id', 1,'partlist', json_array('6fa5181e-5721-11e5-a04e-57f3d7b32808', 'a18437b3-b6c4-4473-a9c5-50e7b8eef6be')));
INSERT INTO big(json) VALUES (json_object('id', 2,'partlist', json_object('uuid','6fa5181e-5721-11e5-a04e-57f3d7b32808')));
INSERT INTO big(json) VALUES (json_object('id', 3,'partlist', json_array(json_object('uuid','e7e3845d-cdfe-48aa-877f-9121b970761d'),json_object('uuid','6fa5181e-5721-11e5-a04e-57f3d7b32808'))));
如果想要找出 ‘$.partlist’ 元素中任意节点包含一个或多个 uuid 为 ‘6fa5181e-5721-11e5-a04e-57f3d7b32808’ 的文档，可以使用以下语句：
SELECT DISTINCT json_extract(big.json,'$.id')
FROM big, json_tree(big.json, '$.partlist')
WHERE json_tree.key='uuid'
AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
json_extract(big.json,'$.id')|
-----------------------------|
2|
3|
编译 JSON1 插件
SQLite 可加载扩展文档描述了如何将可加载扩展编译为共享库。文档中描述的方法也适用于 json1 模块。
json1 源代码包含在 SQLite 程序包中，默认没有启用编译。可以使用 -DSQLITE_ENABLE_JSON1 编译时选项启用 json1 扩展。编译命令行工具和测试工具时，标准的 makefile 中包含了该选项，所以命令行工具可以使用 json1。
版本支持
json1 扩展使用了 SQLite 3.9.0（2015-10-14）引入了 sqlite3_value_subtype() 和sqlite3_result_subtype() 接口，因此更早版本的 SQLite 无法使用 json1 扩展。
当前的 JSON 库实现使用了一个递归下降语法解析器。为了避免使用过多的堆栈空间，任何超过 2000 层嵌套的 JSON 输入都被视为无效数据。嵌套级别的限制符合 RFC-7159 section 9 规定的 JSON 兼容实现。
总结
本文介绍了 SQLite 中的文档存储功能。我们可以借助于 json1 扩展插件提供的 JSON 函数实现文档数据的存储以及 JSON 文档和 SQL 数据的相互转换，同时还可以利用 SQLite 表达式索引和生成列为 JSON 数据提供索引支持，从而实现了将 SQLite 作为一个文档数据库使用的目的。
本文地址：https://blog.csdn.net/horses/article/details/110274164