SQLite数据库中JSON 函数和运算符(二十七)

返回：SQLite—系列文章目录   

上一篇:维护SQLite的私有分支（二十六）

下一篇：SQLite—系列文章目录   

​1. 概述

默认情况下，SQLite 支持 29 个函数和 2 个运算符 处理 JSON 值。还有两个表值函数可用于分解 JSON 字符串。

有 25 个标量函数和运算符：

1. json(json)
2. jsonb(json)
3. json_array(value1,value2,...)
4. jsonb_array(value1,value2,...)
5. json_array_length(json)
   json_array_length(json,path)
6. json_error_position(json)
7. json_extract(json,path,...)
8. jsonb_extract(json,path,...)
9. json -> path
10. json ->> path
11. json_insert(json,path,value,...)
12. jsonb_insert(json,path,value,...)
13. json_object(label1,value1,...)
14. jsonb_object(label1,value1,...)
15. json_patch(json1,json2)
16. jsonb_patch(json1,json2)
17. json_remove(json,path,...)
18. jsonb_remove(json,path,...)
19. json_replace(json,path,value,...)
20. jsonb_replace(json,path,value,...)
21. json_set(json,path,value,...)
22. jsonb_set(json,path,value,...)
23. json_type(json)
    json_type(json,path)
24. json_valid(json)
    json_valid(json,flags)
25. json_quote(value)

有四个聚合 SQL 函数：

1. json_group_array(value)
2. jsonb_group_array(value)
3. json_group_object(label,value)
4. jsonb_group_object(name,value)

这两个表值函数是：

1. json_each(json)
   json_each(json,path)
2. json_tree(json)
   json_tree(json,path)

2. 在 JSON 支持下编译

默认情况下，JSON 函数和运算符内置于 SQLite 中， 自 SQLite 版本 3.38.0 （2022-02-22） 起。可以省略它们 通过添加 -DSQLITE_OMIT_JSON 编译时选项。之前 版本 3.38.0，JSON 函数是一个扩展，只会 如果 -DSQLITE_ENABLE_JSON1 编译时选项包含在构建中 被包括在内。换句话说，JSON 函数从 选择加入 SQLite 版本 3.37.2 及更早版本以选择退出 SQLite 版本 3.38.0 及更高版本。

3. 接口概述

SQLite将JSON存储为普通文本。 向后兼容性约束意味着 SQLite 只能 存储 NULL、整数、浮点数、文本、 和 BLOB。无法添加新的“JSON”类型。

3.1. JSON参数

对于接受 JSON 作为其第一个参数的函数，该参数 可以是 JSON 对象、数组、数字、字符串或 null。SQLite 数值 values 和 NULL 值分别解释为 JSON 编号和 null。 SQLite 文本值可以理解为 JSON 对象、数组或字符串。 如果 SQLite 文本值不是格式正确的 JSON 对象、数组或 字符串被传递到 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.2. JSONB的

从版本 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 格式。

3.2.1. JSONB格式

JSONB 是 SQLite 和 仅供 SQLite 内部使用。应用 不应在 SQLite 之外使用 JSONB，也不应尝试对 JSONB 格式。

“JSONB”名称的灵感来自 PostgreSQL，但 SQLite 的 JSONB 的磁盘格式与 PostgreSQL 的磁盘格式不同。 这两种格式具有相同的名称，但不兼容二进制文件。 PostgreSQL JSONB 格式声称提供 O（1） 查找对象和数组中的元素。SQLite 的 JSONB 格式没有 这种说法。SQLite 的 JSONB 具有 O（N） 时间复杂度 SQLite中的大多数操作，就像文本JSON一样。JSONB的优势 SQLite 是它比文本 JSON 更小、更快——可能有几个 快几倍。有空间 磁盘上的JSONB格式，以添加增强功能，并且SQLite的未来版本可能会 include 选项来提供 JSONB 中元素的 O（1） 查找，但没有这样的选项 该功能目前可用。

3.2.2. 处理畸形的 JSONB

SQLite 生成的 JSONB 将始终格式正确。如果你 遵循推荐的做法，并 将 JSONB 视为不透明的 BLOB，那么您就不会有任何问题。但 JSONB 只是一个 BLOB，所以一个淘气的程序员可以设计 BLOB 与 JSONB 类似，但在技术上格式不正确。什么时候 格式错误的 JSONB 被馈送到 JSON 函数中，以