Small. Fast. Reliable.
Choose any three.
JSON1扩展
► 目录

1.概述

所述json1扩展是一个可加载的扩展实现15应用程序定义的SQL函数和两个表值函数是用于管理有用JSON存储在SQLite数据库的内容。有十三种标量函数：

  1. json（json）
  2. json_array（value1，value2，...）
  3. json_array_length（json）
    json_array_length（json，path）
  4. json_extract（json，path，...）
  5. json_insert（json，path，value，...）
  6. json_object（label1，value1，...）
  7. json_patch（json 1，json2）
  8. json_remove（json，path，...）
  9. json_replace（json，path，value，...）
  10. json_set（json，path，value，...）
  11. json_type（json）
    json_type（json，路径）
  12. json_valid（json）
  13. json_quote（value）

有两个聚合SQL函数：

  1. json_group_array（value）
  2. json_group_object（名称，值）

这两个表值函数是：

  1. json_each（json）
    json_each（json，路径）
  2. json_tree（json）
    json_tree（json，路径）

2.编译JSON1扩展

在连接扩展文档描述了如何编译加载扩展的共享库。那里描述的技术对于json1模块工作正常。

json1源代码包含在SQLite合并中，但默认情况下已禁用。添加-DSQLITE_ENABLE_JSON1编译时选项以启用内置于amalgamation中的json1扩展。在构建命令行外壳程序时，标准的makefile文件包括-DSQLITE_ENABLE_JSON1 以及一些测试实用程序，因此该扩展名通常在命令行外壳程序中可用。

3.界面概述

json1扩展名（当前）将JSON存储为普通文本。

向后兼容性约束意味着SQLite仅能存储NULL，整数，浮点数，文本和BLOB的值。无法添加第六种“ JSON”类型。

json1扩展名（当前）不支持JSON的二进制编码。实验无法找到比纯文本编码小得多或快得多的二进制编码。（当前实现以超过1 GB / s的速度解析JSON文本。）如果所有json1函数的任何参数为BLOB，则当前都会引发错误，因为BLOB保留用于将来的增强，其中BLOB将存储JSON的二进制编码。

json1扩展名末尾的“ 1”是有意的。设计人员预计，在从json1汲取的教训的基础上，将来会出现不兼容的JSON扩展。一旦获得足够的经验，就可以将某种JSON扩展折叠到SQLite核心中。目前，JSON支持仍然是扩展。

3.1。JSON参数

对于将JSON作为其第一个参数的函数，该参数可以是JSON对象，数组，数字，字符串或null。SQLite数值和NULL值分别解释为JSON编号和null。SQLite文本值可以理解为JSON对象，数组或字符串。如果将不是格式正确的JSON对象，数组或字符串的SQLite文本值传递给json1函数，则该函数通常会引发错误。（此规则的例外是json_valid（）和 json_quote（）。）

为了确定有效性，将忽略JSON输入上的前导和尾随空格。根据JSON规范，内部空格也将被忽略。这些例程完全接受 rfc-7159 JSON语法 -不多也不少。

3.2。PATH参数

对于接受PATH参数的函数，该PATH必须格式正确，否则该函数将引发错误。A-形成阱PATH是与恰好一个“$”字符后面“的零个或更多个实例开始文本值objectlabel ”或‘[ arrayindex ]’。

所述arrayindex通常是一个非负整数Ñ。在这种情况下，所选的数组元素是数组的第N个元素，从左侧的零开始。所述arrayindex也可以是形式“的＃ - ñ ”在这种情况下所选择的元素是Ñ从右侧第。数组的最后一个元素是“ ＃-1 ”。将“＃”字符视为“数组中的元素数”。然后，表达式“＃-1”求值为与数组中最后一个条目相对应的整数。

3.3。VALUE个参数

对于接受“ value ”参数（也显示为“ value1 ”和“ value2 ”）的函数，这些参数通常被理解为带引号的文字字符串，并在结果中成为JSON字符串值。即使输入值字符串看起来像格式正确的JSON，它们仍然会在结果中解释为文字字符串。

但是，如果值参数直接来自另一个json1函数的结果，则该参数应理解为实际JSON，并且将插入完整的JSON而不是带引号的字符串。

例如，在以下对json_object（）的调用中，value 参数看起来像一个格式良好的JSON数组。但是，由于它只是普通的SQL文本，因此将其解释为文字字符串，并以带引号的字符串添加到结果中：

但是，如果外部json_object（）调用中的value参数是另一个json1函数（如json（）或json_array（））的结果，则该值应理解为实际的JSON并按以下方式插入：

需要明确的是：“ json ”参数始终被解释为JSON，无论该参数的值来自何处。但是，“ value ”参数仅在这些参数直接来自另一个json1函数时才被解释为JSON。

3.4。兼容性

json1扩展使用SQLite版本3.9.0（2015-10-14）引入的sqlite3_value_subtype（）和 sqlite3_result_subtype（）接口json1扩展在早期版本的SQLite中将不起作用。

该JSON库的当前实现使用递归下降解析器。为了避免使用过多的堆栈空间，任何具有超过2000个嵌套级别的JSON输入都被视为无效。RFC-7159第9节允许对JSON的兼容实现进行嵌套深度限制 。

4.功能细节

以下各节提供了有关json1扩展中各种功能的操作的更多详细信息。

4.1。json（）函数

json（X）函数验证其参数X是有效的JSON字符串，并返回该JSON字符串的精简版本（已删除所有不必要的空格）。如果X不是格式正确的JSON字符串，则此例程将引发错误。

如果json（X）的参数X包含带有重复标签的JSON对象，则是否保留重复项是不确定的。当前实现保留重复项。但是，此例程的将来增强功能可能会选择静默删除重复项。

例子：

4.2。json_array（）函数

json_array（）SQL函数接受零个或多个参数，并返回由这些参数组成的格式良好的JSON数组。如果json_array（）的任何参数是BLOB，则将引发错误。

SQL类型为TEXT的参数通常会转换为带引号的JSON字符串。但是，如果参数是另一个json1函数的输出，则它将存储为JSON。这样可以嵌套对json_array（）和json_object（）的调用。的JSON（）函数也可用于强制的字符串被识别为JSON。

例子：

4.3。json_array_length（）函数

json_array_length（X）函数返回JSON数组X中的元素数，如果X是除数组之外的某种JSON值，则返回0。json_array_length（X，P）将数组定位在X内的路径P处，并返回该数组的长度；如果路径P定位了除JSON数组以外的元素或X，则返回0；如果路径P未定位其中的任何元素，则返回NULL。 X。如果X格式不正确的JSON或P格式不正确的路径，则会引发错误。

例子：

4.4。json_extract（）函数

json_extract（X，P1，P2，...）从X处格式良好的JSON提取并返回一个或多个值。如果仅提供单个路径P1，则结果的SQL数据类型对于JSON为NULL null，INTEGER或REAL表示JSON数值，INTEGER零表示JSON假值，INTEGER表示JSON真值，引号表示JSON字符串值以及JSON对象和数组值的文本表示形式。如果有多个路径参数（P1，P2等），则此例程返回SQLite文本，该文本是格式正确的JSON数组，其中包含各种值。

例子：

4.5。json_insert（），json_replace和json_set（）函数

json_insert（），json_replace和json_set（）函数都将单个JSON值作为其第一个参数，然后是零对或更多对路径和值对，并返回一个新的JSON字符串，该字符串由path /更新输入的JSON形成值对。这些功能的不同之处仅在于它们处理创建新值和覆盖现有值的方式。

功能覆盖是否已经存在？创建是否不存在？
json_insert（）不是的
json_replace（）是的不
json_set（）是的是的

json_insert（），json_replace（）和json_set（）函数始终使用奇数个参数。第一个参数始终是要编辑的原始JSON。后续参数成对出现，每对中的第一个元素是路径，第二个元素是要在该路径上插入，替换或设置的值。

编辑从左到右顺序进行。由先前的编辑引起的更改可能会影响后续编辑的路径搜索。

如果路径/值对的值是SQLite TEXT值，则通常将其作为带引号的JSON字符串插入，即使该字符串看起来像有效的JSON。但是，如果该值是另一个json1函数（例如json（）或json_array（）或json_object（））的结果，则将其解释为JSON并插入为JSON，以保留其所有子结构。

如果第一个JSON参数的格式不正确，或者任何PATH参数的格式不正确，或者任何参数是BLOB，则这些例程将引发错误。

要使用数组索引为“＃”的json_insert（）将元素附加到数组的末尾。例子：

其他例子：

4.6。json_object（）函数

json_object（）SQL函数接受零对或更多对参数，并返回由这些参数组成的格式正确的JSON对象。每对的第一个参数是标签，每对的第二个参数是值。如果json_object（）的任何参数是BLOB，则将引发错误。

json_object（）函数当前允许重复的标签而不会产生投诉，尽管在将来的增强中可能会更改。

SQL类型为TEXT的参数通常会转换为带引号的JSON字符串，即使输入文本为格式正确的JSON。但是，如果参数是另一个json1函数的直接结果，则将其视为JSON，并保留其所有JSON类型信息和子结构。这样可以嵌套对json_object（）和json_array（）的调用。的JSON（）函数也可用于强制的字符串被识别为JSON。

例子：

4.7。json_patch（）函数

json_patch（T，P）SQL函数运行 RFC-7396 MergePatch算法，将补丁P应用于输入T。返回T的补丁副本。

MergePatch可以添加，修改或删除JSON对象的元素，因此对于JSON对象，json_patch（）例程是json_set（）和json_remove（）的通用替代。但是，MergePatch将JSON数组对象视为原子对象。MergePatch不能追加到数组，也不能修改数组的单个元素。它只能作为单个单元插入，替换或删除整个阵列。因此，当处理包含数组的JSON时，json_patch（）并不那么有用，尤其是具有很多子结构的数组。

例子：

4.8。json_remove（）函数

json_remove（X，P，...）函数将单个JSON值作为其第一个参数，然后是零个或多个路径参数。json_remove（X，P，...）函数返回X参数的副本，其中删除了由路径参数标识的所有元素。选择X中找不到的元素的路径将被忽略。

删除顺序是从左到右。由先前删除引起的更改可能会影响后续参数的路径搜索。

如果在不使用路径参数的情况下调用json_remove（X）函数，则它将返回格式化后的输入X，并删除了多余的空格。

如果第一个参数格式不正确的JSON或后面的任何参数格式都不正确的路径，或者任何参数是BLOB，则json_remove（）函数将引发错误。

例子：

4.9。json_type（）函数

json_type（X）函数返回X的最外层元素的“类型”。json_type（X，P）函数返回X由路径P选择的元素的“类型”。json_type返回（）是以下SQL文本值之一：“ null”，“ true”，“ false”，“ integer”，“ real”，“ text”，“ array”或“ object”。如果json_type（X，P）中的路径P选择了X中不存在的元素，则此函数返回NULL。

如果json_type（）函数的任何参数的格式不正确或为BLOB，则将引发错误。

例子：

4.10。json_valid（）函数

如果参数X是格式正确的JSON，则json_valid（X）函数将返回1；如果参数X不是格式正确的JSON，则json_valid（X）函数将返回0。

例子：

4.11。json_quote（）函数

json_quote（X）函数将SQL值X（数字或字符串）转换为其相应的JSON表示形式。

例子：

4.12。json_group_array（）和json_group_object（）聚合SQL函数

json_group_array（X）函数是一个 聚合SQL函数，该函数返回一个由聚合中所有X值组成的JSON数组。同样，json_group_object（NAME，VALUE）函数返回一个由聚合中的所有NAME / VALUE对组成的JSON对象。

4.13。json_each（）和json_tree（）表值函数

表值函数json_each（X）和json_tree（X）遍历作为其第一个参数提供的JSON值，并为每个元素返回一行。json_each（X）函数仅遍历顶级数组或对象的直接子级，或者仅遍历顶级元素本身（如果顶级元素是原始值）。json_tree（X）函数以递归的方式遍历从顶层元素开始的JSON子结构。

json_each（X，P）和json_tree（X，P）函数的工作方式与它们的一元参数对应项相同，不同之处在于它们将路径P标识的元素视为顶级元素。

json_each（）和json_tree（）返回的表的架构如下： 

创建表json_tree（
    键ANY，-当前元素相对于其父元素的键
    值ANY，-当前元素的值
    输入TEXT，-'object'，'array'，'string'，'integer'等
    原子ANY，-原始类型的值，数组和对象的null
    id INTEGER，此元素的整数ID
    parent INTEGER，-此元素的父级的整数ID
    fullkey TEXT，-描述当前元素的完整路径
    path TEXT，-当前行容器的路径
    json JSON HIDDEN，-第一个输入参数：原始JSON
    root TEXT HIDDEN-第二个输入参数：开始的路径
）;

“键”列是JSON数组元素的整数数组索引和JSON对象元素的文本标签。在所有其他情况下，键列均为NULL。

“ atom”列是与原始元素（JSON数组和对象以外的元素）相对应的SQL值。对于JSON数组或对象，“ atom”列为NULL。“值”列与原始JSON元素的“原子”列相同，但采用数组和对象的文本JSON值。

“类型”（type）列是根据以下类型从（null），“ true”，“ false”，“ integer”，“ real”，“ text”，“ array”，“ object”）获取的SQL文本值。当前的JSON元素。

“ id”列是一个整数，用于标识完整JSON字符串中的特定JSON元素。“ id”整数是一个内部整理编号，其计算值在将来的发行版中可能会发生变化。唯一的保证是每一行的“ id”列都会不同。

对于json_each（），“父”列始终为NULL。对于json_tree（），“父”列是当前元素的父元素的“ id”整数，或者是顶级JSON元素或第二个参数中由根路径标识的元素的NULL。

“ fullkey”列是一个文本路径，用于唯一标识原始JSON字符串中的当前行元素。即使“ root”参数提供了替代的起点，也将返回真正的顶级元素的完整键。

“ path”列是保存当前行的数组或对象容器的路径，或者是在迭代从原始类型开始并因此仅提供一行输出的情况下的当前行的路径。

4.13.1。使用json_each（）和json_tree（）的示例

假设表“ CREATE TABLE user（name，phone）”在user.phone字段中存储零个或多个电话号码作为JSON数组对象。要查找所有具有704区号的电话号码的用户，请执行以下操作： 

SELECT DISTINCT用户名
  FROM用户，json_each（user.phone）
 在json_each.value像'704-％';

现在假设如果用户只有一个电话号码，则user.phone字段包含纯文本，如果用户有多个电话号码，则包含一个JSON数组。提出了相同的问题：“哪个用户的704区号有电话号码？” 但是现在，只有具有两个或多个电话号码的用户才能调用json_each（）函数，因为json_each（）需要格式正确的JSON作为其第一个参数： 

从用户WHERE电话中选择名称，例如“ 704-％”
联盟
选择用户名
  FROM用户，json_each（user.phone）
 在json_valid（user.phone）
   AND json_each.value就像'704-％';

考虑带有“ CREATE TABLE big（json JSON）”的其他数据库。要查看数据的逐行完整分解，请执行以下操作： 

选择big.rowid，全键，值
  从大，json_tree（big.json）
 其中json_tree.type NOT IN（'object'，'array'）;

在上一个版本中，WHERE子句的“ NOT IN（'object'，'array'）类型”一词隐含了容器，只允许通过叶子元素。这样可以达到相同的效果： 

选择big.rowid，全键，原子
  从大，json_tree（big.json）
 原子不为空的地方；

假设BIG表中的每个条目都是一个JSON对象，其中的“ $ .id”字段是唯一的标识符，而“ $ .partlist”字段可以是一个深层嵌套的对象。您想在其“ $ .partlist”中的任何位置查找包含对uuid'6fa5181e-5721-11e5-a04e-57f3d7b32808'的一个或多个引用的每个条目的ID。 

SELECT DISTINCT json_extract（big.json，'$。id'）
  FROM big，json_tree（big.json，'$ .partlist'）
 在json_tree.key ='uuid'
   AND json_tree.value ='6fa5181e-5721-11e5-a04e-57f3d7b32808';