小巧。快速。可靠。
三选二。
三选二。
默认情况下，SQLite 支持三十个函数和两个运算符来处理 JSON 值。还有两个表值函数可用于分解 JSON 字符串。
共有 26 个标量函数和运算符
有四个聚合 SQL 函数
两个表值函数是
从 SQLite 3.38.0 版 (2022-02-22) 开始，JSON 函数和运算符默认内置于 SQLite 中。可以通过添加 -DSQLITE_OMIT_JSON 编译时选项来省略它们。在 3.38.0 版之前，JSON 函数是一个扩展，只有在包含 -DSQLITE_ENABLE_JSON1 编译时选项时才会包含在构建中。换句话说，JSON 函数从 SQLite 3.37.2 版及更早版本的选择加入变成了 SQLite 3.38.0 版及更高版本的可选退出。
SQLite 将 JSON 存储为普通文本。向后兼容性约束意味着 SQLite 只能存储 NULL、整数、浮点数、文本和 BLOB 值。无法添加新的“JSON”类型。
对于将 JSON 作为其第一个参数的函数，该参数可以是 JSON 对象、数组、数字、字符串或 null。SQLite 数值和 NULL 值分别解释为 JSON 数字和 null。SQLite 文本值可以理解为 JSON 对象、数组或字符串。如果将不是格式良好的 JSON 对象、数组或字符串的 SQLite 文本值传递到 JSON 函数，则该函数通常会抛出错误。（此规则的例外是 json_valid()、json_quote() 和 json_error_position()。）
这些例程理解所有 rfc-8259 JSON 语法 以及 JSON5 扩展。这些例程生成的 JSON 文本始终严格符合 规范 JSON 定义，并且不包含任何 JSON5 或其他扩展。读取和理解 JSON5 的能力是在 3.42.0 版 (2023-05-16) 中添加的。早期版本的 SQLite 仅读取规范 JSON。
从 3.45.0 版 (2024-01-15) 开始，SQLite 允许将其 JSON 的内部“解析树”表示形式存储在磁盘上，作为 BLOB，以我们称为“JSONB”的格式存储。通过将 SQLite 的 JSON 内部二进制表示形式直接存储在数据库中，应用程序可以在读取和更新 JSON 值时绕过解析和呈现 JSON 的开销。内部 JSONB 格式也比文本 JSON 使用略少的磁盘空间。
任何将文本 JSON 作为输入接受的 SQL 函数参数也将接受 JSONB 格式的 BLOB。在任何一种情况下，函数的操作方式都相同，除了当输入为 JSONB 时，它会运行得更快，因为它不需要运行 JSON 解析器。
大多数返回 JSON 文本的 SQL 函数都有一个返回等效 JSONB 的对应函数。返回文本格式 JSON 的函数以“json_”开头，而返回二进制 JSONB 格式的函数以“jsonb_".
JSONB 是 SQLite 使用的 JSON 的二进制表示形式，仅供 SQLite 内部使用。应用程序不应在 SQLite 外部使用 JSONB，也不应尝试反向工程 JSONB 格式。
“JSONB”名称的灵感来自 PostgreSQL，但 SQLite 的 JSONB 的磁盘格式与 PostgreSQL 的不同。这两种格式具有相同的名称，但不是二进制兼容的。PostgreSQL JSONB 格式声称提供对象和数组中元素的 O(1) 查找。SQLite 的 JSONB 格式没有这样的说法。SQLite 的 JSONB 对 SQLite 中的大多数操作都具有 O(N) 时间复杂度，就像文本 JSON 一样。SQLite 中 JSONB 的优势在于它比文本 JSON 更小且更快——可能快几倍。磁盘上的 JSONB 格式中有空间添加增强功能，并且 SQLite 的未来版本可能会包含提供 JSONB 中元素的 O(1) 查找的选项，但目前尚无此类功能。
SQLite 生成的 JSONB 将始终格式良好。如果您遵循建议的做法并将 JSONB 视为不透明的 BLOB，那么您将不会遇到任何问题。但是 JSONB 只是一个 BLOB，因此一个恶作剧的程序员可以设计出类似于 JSONB 但在技术上格式错误的 BLOB。当将格式错误的 JSONB 输入 JSON 函数时，可能会发生以下任何情况
SQL 语句可能会中止并显示“JSON 格式错误”错误。
如果 JSONB blob 的格式错误部分不影响答案，则可能会返回正确的答案。
可能会返回愚蠢或无意义的答案。
SQLite 处理无效 JSONB 的方式可能会从 SQLite 的一个版本更改为下一个版本。系统遵循垃圾进/垃圾出的规则：如果您向 JSON 函数提供无效的 JSONB，则会返回无效的答案。如果您对 JSONB 的有效性有疑问，请使用 json_valid() 函数进行验证。
我们确实做出了以下一个承诺：格式错误的 JSONB 永远不会导致内存错误或可能导致漏洞的类似问题。无效的 JSONB 可能会导致疯狂的答案，或者可能导致查询中止，但不会导致崩溃。
对于接受 PATH 参数的函数，该 PATH 必须格式良好，否则该函数将抛出错误。格式良好的 PATH 是一个文本值，以恰好一个“$”字符开头，后跟零个或多个“.objectlabel”或“[arrayindex]”实例。
arrayindex 通常是非负整数 N。在这种情况下，选择的数组元素是从左侧开始的数组的第 N 个元素，从零开始。arrayindex 也可以是“#-N”的形式，在这种情况下，选择的元素是从右侧开始的第 N 个元素。数组的最后一个元素是“#-1”。将“#”字符视为“数组中的元素数”。然后表达式“#-1”计算为对应于数组中最后一个条目的整数。有时将数组索引仅作为#字符是有用的，例如在将值附加到现有的 JSON 数组时
对于接受“value”参数（也显示为“value1”和“value2”）的函数，这些参数通常被理解为文字字符串，这些字符串被引用并在结果中成为 JSON 字符串值。即使输入value字符串看起来像格式良好的 JSON，它们在结果中仍被解释为文字字符串。
但是，如果value参数直接来自另一个 JSON 函数的结果或来自-> 运算符（但不是->> 运算符），则该参数被理解为实际的 JSON，并且插入完整的 JSON 而不是引用的字符串。
例如，在以下对 json_object() 的调用中，value参数看起来像格式良好的 JSON 数组。但是，因为它只是普通的 SQL 文本，所以它被解释为文字字符串并作为引用的字符串添加到结果中
但是，如果外部 json_object() 调用中的value参数是另一个 JSON 函数（如 json() 或 json_array()）的结果，则该值被理解为实际的 JSON 并按此插入
明确地说：“json” 参数始终被解释为 JSON，无论该参数的值来自何处。但“value” 参数仅在这些参数直接来自另一个 JSON 函数或-> 运算符时才被解释为 JSON。
在被解释为 JSON 字符串的 JSON 值参数中，Unicode 转义序列不会被视为与表示的 Unicode 代码点所代表的字符或转义控制字符等效。此类转义序列不会被翻译或特殊处理；它们被 SQLite 的 JSON 函数视为纯文本。
此 JSON 库的当前实现使用递归下降解析器。为了避免使用过多的堆栈空间，任何嵌套级别超过 1000 的 JSON 输入都被视为无效。根据RFC-8259 第 9 节，JSON 的兼容实现允许对嵌套深度进行限制。
从 3.42.0 版本（2023-05-16）开始，这些例程将读取并解释包含JSON5扩展的输入 JSON 文本。但是，这些例程生成的 JSON 文本将始终严格符合JSON 的规范定义。
以下是 JSON5 扩展的概要（改编自JSON5 规范）
要将字符串 X 从 JSON5 转换为规范 JSON，请调用“json(X)”。无论输入中是否存在任何 JSON5 扩展，“json()” 函数的输出都将是规范 JSON。为了向后兼容，没有“flags”参数的json_valid(X) 函数继续对不是规范 JSON 的输入报告 false，即使输入是该函数能够理解的 JSON5 也是如此。要确定输入字符串是否为有效的 JSON5，请在 json_valid 的“flags”参数中包含 0x02 位：json_valid(X,2)".
这些例程理解所有 JSON5，以及更多一点。SQLite 通过以下两种方式扩展了 JSON5 语法
严格的 JSON5 要求不带引号的对象键必须是 ECMAScript 5.1 IdentifierNames。但是，为了确定键是否为 ECMAScript 5.1 IdentifierName，需要大型 Unicode 表格和大量代码。因此，SQLite 允许对象键包含任何大于 U+007f 且不是空白字符的 Unicode 字符。这种宽松的“标识符”定义极大地简化了实现，并使 JSON 解析器更小且运行速度更快。
JSON5 允许将浮点无穷大表示为Infinity", "-Infinity，或+Infinity，并且必须完全按照这种格式 - 最初的“I”大写，所有其他字符小写。SQLite 还允许使用缩写Inf代替Infinity，并且允许这两个关键字以任何大小写字母组合出现。类似地，JSON5 允许使用“NaN”表示非数字。SQLite 将其扩展为还允许使用“QNaN”和“SNaN”，并且可以以任何大小写字母组合出现。请注意，SQLite 将 NaN、QNaN 和 SNaN 解释为“null”的替代拼写。添加此扩展是因为（据我们所知）在现实世界中存在大量包含这些非标准无穷大和非数字表示的 JSON。
大多数 JSON 函数使用 JSONB 进行内部处理。因此，如果输入是文本，它们首先会将输入文本转换为 JSONB。如果输入已经采用 JSONB 格式，则无需进行转换，可以跳过此步骤，并且性能更快。
因此，当一个 JSON 函数的参数由另一个 JSON 函数提供时，通常使用jsonb_”作为参数使用的函数的变体效率更高。
聚合 JSON SQL 函数是此规则的例外。这些函数都使用文本而不是 JSONB 进行处理。因此，对于聚合 JSON SQL 函数，使用“json_”函数而不是“jsonb_”函数提供参数效率更高。
如果 JSON 输入是一个不是 JSONB 的 BLOB，并且在转换为文本时看起来像文本 JSON，则将其接受为文本 JSON。这实际上是原始实现中长期存在的错误，SQLite 开发人员没有意识到。文档指出，JSON 函数的 BLOB 输入应该引发错误。但在实际实现中，只要 BLOB 内容是数据库文本编码中有效的 JSON 字符串，就会接受输入。
在 3.45.0 版本（2024-01-15）重新实现 JSON 例程时，意外修复了此 JSON BLOB 输入错误。这导致依赖旧行为的应用程序出现故障。（为这些应用程序辩护：它们通常会被readfile() SQL 函数（在CLI 中可用）吸引使用 BLOB 作为 JSON。Readfile() 用于从磁盘文件读取 JSON，但 readfile() 返回 BLOB。这对它们有效，所以为什么不这样做呢？）
为了向后兼容，特此记录（以前不正确的）将 BLOB 解释为文本 JSON 的旧有行为（如果没有任何其他解释方法），并在 3.45.1 版本（2024-01-30）及所有后续版本中正式支持。
以下部分提供了有关各种 JSON 函数和运算符操作的更多详细信息：
json(X) 函数验证其参数 X 是否为有效的 JSON 字符串或 JSONB blob，并返回该 JSON 字符串的最小化版本，其中所有不必要的空格都被删除。如果 X 不是格式良好的 JSON 字符串或 JSONB blob，则此例程会抛出错误。
如果输入是 JSON5 文本，则在返回之前将其转换为规范的 RFC-8259 文本。
如果 json(X) 的参数 X 包含具有重复标签的 JSON 对象，则保留重复项与否是不确定的。当前实现保留重复项。但是，此例程的未来增强可能会选择静默删除重复项。
示例
jsonb(X) 函数返回作为参数 X 提供的 JSON 的二进制 JSONB 表示形式。如果 X 是不具有有效 JSON 语法的 TEXT，则会引发错误。
如果 X 是 BLOB 并且看起来像 JSONB，则此例程仅返回 X 的副本。但是，只会检查 JSONB 的最外层元素。不会验证 JSONB 的深层结构。
json_array() SQL 函数接受零个或多个参数，并返回一个由这些参数组成的格式良好的 JSON 数组。如果 json_array() 的任何参数是 BLOB，则会抛出错误。
SQL 类型为 TEXT 的参数通常会转换为带引号的 JSON 字符串。但是，如果参数是另一个 json1 函数的输出，则将其存储为 JSON。这允许嵌套调用 json_array() 和json_object()。还可以使用json() 函数强制将字符串识别为 JSON。
示例
jsonb_array() SQL 函数的工作方式与json_array() 函数完全相同，只是它以 SQLite 的私有 JSONB 格式而不是标准 RFC 8259 文本格式返回构造的 JSON 数组。
json_array_length(X) 函数返回 JSON 数组 X 中元素的数量，如果 X 是数组以外的某种 JSON 值，则返回 0。json_array_length(X,P) 在 X 中路径 P 处定位数组并返回该数组的长度，如果路径 P 定位 X 中不是 JSON 数组的元素，则返回 0，如果路径 P 未定位 X 的任何元素，则返回 NULL。如果 X 不是格式良好的 JSON 或 P 不是格式良好的路径，则会抛出错误。
示例
如果输入 X 是格式良好的 JSON 或 JSON5 字符串，则 json_error_position(X) 函数返回 0。如果输入 X 包含一个或多个语法错误，则此函数返回第一个语法错误的字符位置。最左边的字符是位置 1。
如果输入 X 是 BLOB，则如果 X 是格式良好的 JSONB blob，则此例程返回 0。如果返回值为正，则它表示检测到的第一个错误在 BLOB 中的大约基于 1 的位置。
json_error_position() 函数是在 SQLite 3.42.0 版本（2023-05-16）中添加的。
json_extract(X,P1,P2,...) 从 X 处的格式良好的 JSON 中提取并返回一个或多个值。如果只提供单个路径 P1，则结果的 SQL 数据类型为 JSON null 为 NULL，JSON 数值为 INTEGER 或 REAL，JSON false 值为 INTEGER 零，JSON true 值为 INTEGER 一，JSON 字符串值为去引号的文本，JSON 对象和数组值为文本表示形式。如果有多个路径参数（P1、P2 等），则此例程返回 SQLite 文本，这是一个包含各种值的格式良好的 JSON 数组。
示例
SQLite 中的 json_extract() 函数与 MySQL 中的 json_extract() 函数之间存在细微的兼容性问题。MySQL 版本的 json_extract() 始终返回 JSON。SQLite 版本的 json_extract() 仅当存在两个或更多 PATH 参数（因为结果为 JSON 数组）或单个 PATH 参数引用数组或对象时才返回 JSON。在 SQLite 中，如果 json_extract() 只有一个 PATH 参数，并且该 PATH 引用 JSON null、字符串或数值，则 json_extract() 返回相应的 SQL NULL、TEXT、INTEGER 或 REAL 值。
MySQL json_extract() 和 SQLite json_extract() 之间的差异仅在访问 JSON 中为字符串或 NULL 的单个值时才会显现出来。下表演示了这种差异
| 操作 | SQLite 结果 | MySQL 结果 |
|---|---|---|
| json_extract('{"a":null,"b":"xyz"}','$.a') | NULL | 'null' |
| json_extract('{"a":null,"b":"xyz"}','$.b') | 'xyz' | '"xyz"' |
jsonb_extract() 函数的工作方式与 json_extract() 函数相同，除了在 json_extract() 通常返回文本 JSON 数组对象的情况下，此例程以 JSONB 格式返回数组或对象。对于通常返回文本、数字、null 或布尔 JSON 元素的情况，此例程的工作方式与 json_extract() 完全相同。
从 SQLite 3.38.0 版本（2022-02-22）开始，-> 和 ->> 运算符可用于提取 JSON 的子组件。SQLite 中 -> 和 ->> 的实现力求与 MySQL 和 PostgreSQL 兼容。-> 和 ->> 运算符以 JSON 字符串或 JSONB blob 作为其左操作数，并以 PATH 表达式或对象字段标签或数组索引作为其右操作数。-> 运算符返回所选子组件的文本 JSON 表示形式，如果该子组件不存在则返回 NULL。->> 运算符返回表示所选子组件的 SQL TEXT、INTEGER、REAL 或 NULL 值，如果子组件不存在则返回 NULL。
-> 和 ->> 运算符都选择其左侧 JSON 的相同子组件。不同之处在于 -> 始终返回该子组件的 JSON 表示形式，而 ->> 运算符始终返回该子组件的 SQL 表示形式。因此，这些运算符与带有两个参数的 json_extract() 函数调用略有不同。如果子组件是 JSON 数组或对象，则带有两个参数的 json_extract() 函数调用将返回该子组件的 JSON 表示形式；如果子组件是 JSON null、字符串或数值，则将返回该子组件的 SQL 表示形式。
当 -> 运算符返回 JSON 时，它始终返回该 JSON 的 RFC 8565 文本表示形式，而不是 JSONB。如果您需要 JSONB 格式的子组件，请使用 jsonb_extract() 函数。
-> 和 ->> 运算符的右操作数可以是格式良好的 JSON 路径表达式。这是 MySQL 使用的形式。为了与 PostgreSQL 保持兼容，-> 和 ->> 运算符还接受文本对象标签或整数数组索引作为其右操作数。如果右操作数是文本标签 X，则将其解释为 JSON 路径 '$.X'。如果右操作数是整数 N，则将其解释为 JSON 路径 '$[N]'。
示例
json_insert()、json_replace() 和 json_set() 函数都将单个 JSON 值作为其第一个参数，后跟零个或多个路径和值参数对，并返回一个新的 JSON 字符串，该字符串通过路径/值对更新输入 JSON 形成。这些函数仅在处理创建新值和覆盖预先存在的值方面有所不同。
| 函数 | 如果已存在则覆盖？ | 如果不存在则创建？ |
|---|---|---|
| json_insert() | 否 | 是 |
| json_replace() | 是 | 否 |
| json_set() | 是 | 是 |
json_insert()、json_replace() 和 json_set() 函数始终采用奇数个参数。第一个参数始终是要编辑的原始 JSON。后续参数成对出现，每对中的第一个元素是路径，第二个元素是要插入或替换或设置在该路径上的值。
编辑按从左到右的顺序进行。先前编辑导致的更改可能会影响后续编辑的路径搜索。
如果路径/值对的值是 SQLite TEXT 值，则通常将其插入为带引号的 JSON 字符串，即使该字符串看起来像有效的 JSON。但是，如果该值是另一个 json 函数（例如 json() 或 json_array() 或 json_object()）的结果，或者它是 -> 运算符 的结果，则将其解释为 JSON 并作为 JSON 插入，保留其所有子结构。 ->> 运算符 的结果值始终被解释为 TEXT，并作为 JSON 字符串插入，即使它们看起来像有效的 JSON。
如果第一个 JSON 参数格式不正确，或者任何 PATH 参数格式不正确，或者任何参数是 BLOB，则这些例程会抛出错误。
要将元素追加到数组的末尾，请使用带有“#”数组索引的 json_insert()。示例
其他示例
jsonb_insert()、jsonb_replace() 和 jsonb_set() 函数的工作方式分别与 json_insert()、json_replace() 和 json_set() 相同，只是“jsonb_”版本以二进制 JSONB 格式返回其结果。
json_object() SQL 函数接受零个或多个参数对，并返回一个由这些参数组成的格式良好的 JSON 对象。每对中的第一个参数是标签，每对中的第二个参数是值。如果 json_object() 的任何参数是 BLOB，则会抛出错误。
json_object() 函数目前允许重复标签，但不会报错，不过这可能会在将来的增强功能中发生变化。
SQL 类型为 TEXT 的参数通常会转换为带引号的 JSON 字符串，即使输入文本是格式良好的 JSON 也是如此。但是，如果该参数是另一个 JSON 函数或 -> 运算符（但不是 ->> 运算符）的直接结果，则将其视为 JSON，并保留其所有 JSON 类型信息和子结构。这允许嵌套调用 json_object() 和 json_array()。 json() 函数也可用于强制识别字符串为 JSON。
示例
jsonb_object() 函数的工作方式与 json_object() 函数完全相同，只是生成的 object 以二进制 JSONB 格式返回。
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() 并不那么有用，尤其是包含大量子结构的数组。
示例
jsonb_patch() 函数的工作方式与 json_patch() 函数完全相同，只是已修补的 JSON 以二进制 JSONB 格式返回。
json_pretty() 函数的工作方式与 json() 相同，只是它添加了额外的空格以使 JSON 结果更易于人类阅读。第一个参数是要进行漂亮打印的 JSON 或 JSONB。可选的第二个参数是用于缩进的文本字符串。如果省略第二个参数或为 NULL，则每级缩进四个空格。
json_pretty() 函数是在 SQLite 3.46.0 版本（2024-05-23）中添加的。
json_remove(X,P,...) 函数以单个 JSON 值作为其第一个参数，后跟零个或多个路径参数。json_remove(X,P,...) 函数返回 X 参数的副本，其中已删除由路径参数标识的所有元素。选择 X 中未找到的元素的路径将被静默忽略。
移除操作按从左到右的顺序进行。先前移除操作导致的更改可能会影响后续参数的路径搜索。
如果 json_remove(X) 函数在没有路径参数的情况下被调用，则它会返回重新格式化的输入 X，并删除多余的空格。
如果第一个参数不是格式良好的 JSON，或者任何后续参数不是格式良好的路径，则 json_remove() 函数会抛出错误。
示例
jsonb_remove() 函数的工作方式与 json_remove() 函数完全相同，只是编辑后的 JSON 结果以二进制 JSONB 格式返回。
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() 函数的第一个参数不是格式良好的 JSON 或 JSONB，或者第二个参数不是格式良好的 JSON 路径，则它会抛出错误。
示例
如果参数 X 是格式良好的 JSON，则 json_valid(X,Y) 函数返回 1；如果 X 不是格式良好的，则返回 0。Y 参数是一个整数位掩码，用于定义“格式良好”的含义。Y 的以下位目前已定义
通过组合位，可以得出以下有用的 Y 值
Y 参数是可选的。如果省略，则默认为 1，这意味着默认行为是仅当输入 X 是严格符合 RFC-8259 JSON 文本且没有任何扩展时才返回 true。这使得 json_valid() 的单参数版本与 SQLite 的旧版本兼容，在添加对 JSON5 和 JSONB 的支持之前。
Y 参数中 0x04 和 0x08 位之间的区别在于，0x04 仅检查 BLOB 的外部包装以查看其是否表面上看起来像 JSONB。这对于大多数用途来说已经足够了，而且速度很快。0x08 位会彻底检查 BLOB 的所有内部细节。0x08 位需要的时间与 X 输入的大小成线性关系，并且速度慢得多。对于大多数用途，建议使用 0x04 位。
如果您只想了解某个值是否可以作为其他 JSON 函数的合理输入，则可能需要使用 Y 值 6。
对于最新版本的 json_valid()，任何小于 1 或大于 15 的 Y 值都会引发错误。但是，json_valid() 的未来版本可能会增强以接受此范围之外的标志值，并具有我们尚未想到的新含义。
如果 json_valid() 的 X 或 Y 输入为 NULL，则函数返回 NULL。
示例
json_quote(X) 函数将 SQL 值 X（数字或字符串）转换为其对应的 JSON 表示形式。如果 X 是由另一个 JSON 函数返回的 JSON 值，则此函数什么也不做。
示例
json_group_array(X) 函数是一个 聚合 SQL 函数，它返回一个由聚合中所有 X 值组成的 JSON 数组。类似地，json_group_object(NAME,VALUE) 函数返回一个由聚合中所有 NAME/VALUE 对组成的 JSON 对象。"jsonb_" 变体与之相同，只是它们以二进制 JSONB 格式返回结果。
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() 返回的表的模式如下
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 列均为 NULL。
"atom" 列是对应于原始元素的 SQL 值——除了 JSON 数组和对象之外的元素。"atom" 列对于 JSON 数组或对象为 NULL。"value" 列对于原始 JSON 元素与 "atom" 列相同，但对于数组和对象则采用文本 JSON 值。
"type" 列是根据当前 JSON 元素的类型从 ('null'、'true'、'false'、'integer'、'real'、'text'、'array'、'object') 中获取的 SQL 文本值。
"id" 列是一个整数，用于标识完整 JSON 字符串中的特定 JSON 元素。"id" 整数是一个内部管理编号，其计算方式可能会在将来的版本中更改。唯一保证的是，每个行的 "id" 列都将不同。
"parent" 列对于 json_each() 始终为 NULL。对于 json_tree()，"parent" 列是当前元素的父元素的 "id" 整数，或者对于顶级 JSON 元素或第二个参数中根路径标识的元素为 NULL。
"fullkey" 列是一个文本路径，用于唯一标识原始 JSON 字符串中的当前行元素。即使“root”参数提供了替代的起始点，也会返回到真正顶级元素的完整键。
"path" 列是包含当前行的数组或对象容器的路径，或者在迭代从原始类型开始并且因此仅提供一行输出的情况下，是当前行的路径。
假设表“CREATE TABLE user(name,phone)”在 user.phone 字段中将零个或多个电话号码存储为 JSON 数组对象。要查找所有电话号码中包含 704 区号的任何用户的电话号码
SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%';
现在假设 user.phone 字段如果用户只有一个电话号码则包含纯文本，如果用户有多个电话号码则包含 JSON 数组。提出相同的问题：“哪些用户在 704 区号内有电话号码？”但是现在只能对那些有两个或多个电话号码的用户调用 json_each() 函数，因为 json_each() 要求其第一个参数为格式良好的 JSON
SELECT name FROM user WHERE phone LIKE '704-%' UNION SELECT user.name FROM user, json_each(user.phone) WHERE json_valid(user.phone) AND json_each.value LIKE '704-%';
考虑一个不同的数据库，其中“CREATE TABLE big(json JSON)”。要查看数据的完整逐行分解
SELECT big.rowid, fullkey, value
FROM big, json_tree(big.json)
WHERE json_tree.type NOT IN ('object','array');
在前面，WHERE 子句的“type NOT IN ('object','array')”项抑制容器，只允许叶元素通过。可以通过以下方式实现相同的效果
SELECT big.rowid, fullkey, atom FROM big, json_tree(big.json) WHERE atom IS NOT NULL;
假设 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') WHERE json_tree.key='uuid' AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
此页面上次修改于 2024-07-25 15:06:57 UTC