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

原创

已于 2024-04-17 15:04:11 修改 · 3.1k 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#c++ #sqlite #数据库

于 2024-04-16 08:44:17 首次发布

1. 概述

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

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

json(json)
jsonb(json)
json_array(value1,value2,...)
jsonb_array(value1,value2,...)
json_array_length(json)
json_array_length(json,path)
json_error_position(json)
json_extract(json,path,...)
jsonb_extract(json,path,...)
json -> path
json ->> path
json_insert(json,path,value,...)
jsonb_insert(json,path,value,...)
json_object(label1,value1,...)
jsonb_object(label1,value1,...)
json_patch(json1,json2)
jsonb_patch(json1,json2)
json_remove(json,path,...)
jsonb_remove(json,path,...)
json_replace(json,path,value,...)
jsonb_replace(json,path,value,...)
json_set(json,path,value,...)
jsonb_set(json,path,value,...)
json_type(json)
json_type(json,path)
json_valid(json)
json_valid(json,flags)
json_quote(value)

有四个聚合 SQL 函数：

json_group_array(value)
jsonb_group_array(value)
json_group_object(label,value)
jsonb_group_object(name,value)

这两个表值函数是：

json_each(json)
json_each(json,path)
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 格式。