---

SQLite JSON 数据操作完全指南

在现代数据存储和交换中，JSON（JavaScript Object Notation）已成为一种极其流行的数据格式。它以其简洁、灵活和易于理解的特性，广泛应用于Web服务、配置管理以及非结构化或半结构化数据的存储。许多数据库系统都提供了对JSON数据的原生支持，SQLite作为一款轻量级、嵌入式关系型数据库，也不例外。

从版本 3.9.0 (2015-12-10) 开始，SQLite 开始集成 JSON 函数，并在后续版本中不断增强其功能。现在，SQLite 提供了一套丰富、高效的函数，允许你在数据库中存储、查询、创建和修改 JSON 数据，而无需将数据完全提取到应用程序层面进行处理。

本文将带你深入了解SQLite的JSON功能，包括如何存储JSON、常用的JSON函数、如何查询和修改JSON数据、如何利用SQL生成JSON输出，以及一些性能考虑和最佳实践。

1. SQLite 中的 JSON 支持概述

SQLite 将 JSON 数据视为特殊的文本字符串进行处理。它提供了一系列内置的SQL函数，这些函数能够解析JSON字符串，提取其中的值，检查其有效性，甚至修改或构建JSON结构。这些函数通常以 json_ 为前缀。

核心优势：

• 灵活性： 可以在同一个表中存储具有不同结构的JSON文档，适用于需要灵活模式的应用场景。
• 无需外部库： SQLite内置了JSON解析和处理逻辑，无需依赖额外的扩展或库。
• SQL集成： 可以直接在标准的SQL查询中结合使用JSON函数和关系型操作（如WHERE子句、JOIN、GROUP BY等）。
• 性能优化： 尽管JSON是文本，但SQLite的JSON函数经过优化，效率较高，并且可以通过索引等技术进一步提升查询性能。

版本要求：

大多数核心JSON函数 (json_extract, json_valid, json_array_length, json_type) 在 SQLite 3.9.0 及更高版本中可用。json_insert, json_replace, json_set, json_remove 在 3.18.0 (2017-05-22) 引入。更强大的函数如 json_each 和 json_tree 在 3.22.0 (2018-01-22) 引入。json_patch (JSON Merge Patch) 在 3.38.0 (2022-02-22) 引入。生成列（Generated Columns，可用于索引JSON路径）在 3.31.0 (2020-01-22) 引入。建议使用较新版本的SQLite以获得完整功能和最佳性能。

默认情况下，大多数预编译的SQLite库都包含了JSON支持。如果需要自己编译SQLite，通常需要确保开启 --enable-json 选项。

2. 在 SQLite 中存储 JSON 数据

最简单也是最常见的方式是将完整的JSON文档存储在一个 TEXT 类型的列中。

“`sql
— 创建一个用于存储文档数据的表
CREATE TABLE documents (
id INTEGER PRIMARY KEY AUTOINCREMENT,
doc_data TEXT
);

— 插入一些包含JSON数据的行
INSERT INTO documents (doc_data) VALUES
(
‘{
“name”: “Alice”,
“age”: 30,
“isStudent”: false,
“courses”: [“Math”, “Physics”],
“address”: {
“city”: “Wonderland”,
“zip”: “12345”
}
}’
),
(
‘{
“name”: “Bob”,
“occupation”: “Engineer”,
“skills”: [“Python”, “SQL”, “JSON”],
“contact”: {
“email”: “[email protected]”
}
}’
),
(
‘{
“id”: 101,
“status”: “active”,
“tags”: null
}’
);
“`

在这个例子中，doc_data 列存储了不同结构但都是有效JSON格式的字符串。

最佳实践：

• 使用 TEXT 类型存储完整的JSON文档。
• 考虑对存储的JSON进行验证，可以使用 json_valid() 函数在 CHECK 约束中强制执行JSON有效性（尽管这可能会影响插入性能）。

sql
-- 添加CHECK约束以确保数据是有效JSON
-- 这可能会在每次插入/更新时增加开销
-- ALTER TABLE documents ADD CHECK (json_valid(doc_data));
实际应用中，通常在应用程序层面或通过触发器来处理更复杂的验证逻辑。

3. 核心 JSON 函数

SQLite 提供了一系列函数来处理 JSON 数据。以下是一些最常用和最基础的函数：

3.1 json_valid(json)

用途： 检查一个字符串是否是有效的JSON格式。
返回值： 如果字符串是有效的JSON，返回1；否则返回0。

sql
SELECT json_valid('{"a": 1}'); -- 输出: 1
SELECT json_valid('[1, 2, 3]'); -- 输出: 1
SELECT json_valid('"hello"'); -- 输出: 1
SELECT json_valid('true'); -- 输出: 1
SELECT json_valid('123'); -- 输出: 1
SELECT json_valid('null'); -- 输出: 1
SELECT json_valid('{"a": 1, "b":}'); -- 输出: 0 (无效的JSON)
SELECT json_valid('abc'); -- 输出: 0 (不是JSON)

3.2 json_extract(json, path1, path2, ...)

用途： 从JSON字符串中提取指定路径的值。这是用于读取JSON数据最核心的函数。
返回值： 返回提取到的值。如果路径不存在或无效，返回 NULL。返回的值类型为 TEXT（对于字符串、数字、布尔、null）、BLOB（对于嵌套对象或数组，表示其JSON字符串形式）。

JSON Path 语法：
JSON Path 是一种用于定位JSON结构中元素的语言。SQLite支持一种简化版的JSON Path语法：

• $: 表示根元素（整个JSON文档）。
• .key: 访问对象的成员。例如 $.name。
• [index]: 访问数组的元素（索引从0开始）。例如 $.courses[0]。
• 可以使用点和方括号组合访问嵌套结构。例如 $.address.city, $.courses[1].
• 多个路径可以作为参数传递，结果将是一个包含所有提取值的JSON数组。

示例：

“`sql
— 从第一行数据中提取 name 和 age
SELECT
json_extract(doc_data, ‘$.name’) AS person_name,
json_extract(doc_data, ‘$.age’) AS person_age
FROM documents
WHERE id = 1;
— 输出:
— person_name | person_age
— ———– | ———-
— Alice | 30

— 提取嵌套对象中的值
SELECT json_extract(doc_data, ‘$.address.city’) AS city
FROM documents
WHERE id = 1;
— 输出:
— city

---

— Wonderland

— 提取数组的第一个元素
SELECT json_extract(doc_data, ‘$.courses[0]’) AS first_course
FROM documents
WHERE id = 1;
— 输出:
— first_course

---

— Math

— 提取整个嵌套对象或数组（返回其JSON文本表示）
SELECT
json_extract(doc_data, ‘$.address’) AS full_address_json,
json_extract(doc_data, ‘$.courses’) AS courses_array_json
FROM documents
WHERE id = 1;
— 输出:
— full_address_json | courses_array_json
— ————————- | ——————
— {“city”:”Wonderland”, … | [“Math”,”Physics”]

— 提取多个路径的值（结果是一个JSON数组）
SELECT json_extract(doc_data, ‘$.name’, ‘$.address.city’, ‘$.courses[1]’) AS extracted_values
FROM documents
WHERE id = 1;
— 输出:
— extracted_values

---

— [“Alice”,”Wonderland”,”Physics”]

— 提取不存在的路径（返回NULL）
SELECT json_extract(doc_data, ‘$.email’) AS non_existent_value
FROM documents
WHERE id = 1;
— 输出:
— non_existent_value

---

— NULL
“`

重要提示： json_extract 返回的值类型是 TEXT 或 BLOB。当提取数字、布尔或 null 时，它们会被转换为相应的文本 (“123”, “true”, “false”, “null”)。在进行数值比较或排序时，可能需要显式进行类型转换（尽管SQLite的动态类型系统在很多情况下会自动处理）。

3.3 json_array_length(json, path)

用途： 返回指定路径的JSON数组的长度。
参数： path 参数是可选的，如果省略，则假定根元素是一个数组。
返回值： 如果路径指定一个数组，返回其元素个数；如果路径不存在或指定的不是数组，返回 NULL。

“`sql
— 获取 courses 数组的长度
SELECT json_array_length(doc_data, ‘$.courses’) AS courses_count
FROM documents
WHERE id = 1;
— 输出:
— courses_count

---

— 2

— 获取 skills 数组的长度
SELECT json_array_length(doc_data, ‘$.skills’) AS skills_count
FROM documents
WHERE id = 2;
— 输出:
— skills_count

---

— 3

— 对非数组路径或不存在的路径使用 json_array_length
SELECT
json_array_length(doc_data, ‘$.name’) AS name_length, — name 是字符串，非数组
json_array_length(doc_data, ‘$.nonexistent’) AS nonexistent_length — 路径不存在
FROM documents
WHERE id = 1;
— 输出:
— name_length | nonexistent_length
— ———– | ——————
— NULL | NULL
“`

3.4 json_type(json, path)

用途： 返回指定路径的JSON值的类型。
参数： path 参数是可选的，如果省略，则假定需要判断根元素的类型。
返回值： 返回类型的文本表示，如 ‘object’, ‘array’, ‘string’, ‘integer’, ‘real’, ‘true’, ‘false’, ‘null’。如果路径不存在或无效，返回 NULL。

“`sql
— 获取不同路径的值类型
SELECT
json_type(doc_data, ‘$’) AS root_type,
json_type(doc_data, ‘$.name’) AS name_type,
json_type(doc_data, ‘$.age’) AS age_type,
json_type(doc_data, ‘$.isStudent’) AS isstudent_type,
json_type(doc_data, ‘$.courses’) AS courses_type,
json_type(doc_data, ‘$.address’) AS address_type,
json_type(doc_data, ‘$.tags’) AS tags_type, — null 值
json_type(doc_data, ‘$.nonexistent’) AS nonexistent_type — 不存在的路径
FROM documents
WHERE id = 1;
— 输出:
— root_type | name_type | age_type | isstudent_type | courses_type | address_type | tags_type | nonexistent_type
— ——— | ——— | ——– | ————– | ———— | ———— | ——— | —————-
— object | string | integer | false | array | object | null | NULL

— 另一个例子
SELECT
json_type(doc_data, ‘$.id’) AS id_type,
json_type(doc_data, ‘$.status’) AS status_type,
json_type(doc_data, ‘$.tags’) AS tags_type
FROM documents
WHERE id = 3;
— 输出:
— id_type | status_type | tags_type
— ——- | ———– | ———
— integer | string | null
``
请注意，JSON标准没有区分整数和浮点数类型，都归类为"number"。然而，SQLite的json_type` 函数会区分 ‘integer’ 和 ‘real’。对于布尔值，返回 ‘true’ 或 ‘false’。对于null，返回 ‘null’。这些都是小写字符串。

3.5 json(json)

用途： 解析JSON字符串并返回一个规范化的JSON表示。它可以用来验证JSON或将提取的BLOB值转换回可用的JSON文本。
返回值： 如果输入是有效的JSON，返回规范化的JSON字符串（类型为BLOB，但通常被视为TEXT）。如果输入无效，返回 NULL。

“`sql
— 验证并规范化JSON
SELECT json(‘ { “a” : 1 , “b” : “hello” } ‘);
— 输出: {“a”:1,”b”:”hello”} (可能返回BLOB，但客户端通常显示为TEXT)

— 结合 json_extract 提取嵌套结构并用 json() 转换回TEXT
SELECT json(json_extract(doc_data, ‘$.address’)) AS address_text
FROM documents
WHERE id = 1;
— 输出:
— address_text

---

— {“city”:”Wonderland”,”zip”:”12345″}
``json()函数在某些场景下非常有用，特别是当你从json_extract` 得到一个表示嵌套对象或数组的BLOB，需要将其作为TEXT来进一步处理或显示时。

4. 查询 JSON 数据

利用 json_extract 和其他函数，你可以轻松地在 WHERE 子句、ORDER BY 子句或 SELECT 列表中查询、过滤和排序基于JSON内容的行。

“`sql
— 查询所有住在 “Wonderland” 的人
SELECT id, json_extract(doc_data, ‘$.name’) AS name
FROM documents
WHERE json_extract(doc_data, ‘$.address.city’) = ‘Wonderland’;
— 输出:
— id | name
— — | —-
— 1 | Alice

— 查询所有年龄小于 35 的人
SELECT id, json_extract(doc_data, ‘$.name’) AS name, json_extract(doc_data, ‘$.age’) AS age
FROM documents
WHERE json_extract(doc_data, ‘$.age’) < 35;
— 输出:
— id | name | age
— — | —– | —
— 1 | Alice | 30

— 查询包含特定技能的人（例如，包含 “SQL” 技能）
— 需要结合 json_each 或 LIKE/GLOB (不推荐后者)
— 使用 json_each (后面会详细介绍)
SELECT T1.id, json_extract(T1.doc_data, ‘$.name’) AS name
FROM documents AS T1, json_each(T1.doc_data, ‘$.skills’) AS T2
WHERE T2.value = ‘SQL’;
— 输出:
— id | name
— — | —-
— 2 | Bob

— 按姓名排序结果
SELECT
id,
json_extract(doc_data, ‘$.name’) AS name,
json_extract(doc_data, ‘$.age’) AS age
FROM documents
WHERE json_extract(doc_data, ‘$.age’) IS NOT NULL — 过滤掉没有age字段的行
ORDER BY json_extract(doc_data, ‘$.name’);
— 输出:
— id | name | age
— — | —– | —
— 1 | Alice | 30
— 2 | Bob | NULL — Bob没有age，但在ORDER BY时NULL通常排在最前或最后，取决于实现和NULLS FIRST/LAST

— 如果需要按age排序
SELECT
id,
json_extract(doc_data, ‘$.name’) AS name,
json_extract(doc_data, ‘$.age’) AS age
FROM documents
WHERE json_extract(doc_data, ‘$.age’) IS NOT NULL
ORDER BY json_extract(doc_data, ‘$.age’);
— 输出:
— id | name | age
— — | —– | —
— 1 | Alice | 30

— 查询 tags 字段为 null 的行
SELECT id, json_extract(doc_data, ‘$.id’) AS doc_id
FROM documents
WHERE json_extract(doc_data, ‘$.tags’) IS NULL;
— 输出:
— id | doc_id
— — | ——
— 3 | 101
“`

4.1 使用 json_each() 和 json_tree() 进行遍历

json_each(json, path) 和 json_tree(json, path) 是两个非常有用的表值函数，它们可以将JSON对象或数组展开为一系列行，便于查询和分析嵌套结构。

• json_each(json, path): 遍历指定路径下的直接子元素（对象键值对或数组元素）。
• json_tree(json, path): 递归遍历指定路径下的所有子元素。

这两个函数都返回一个虚拟表，包含以下列：

• key: 如果是对象，返回键名；如果是数组，返回元素的索引（从0开始）。
• value: 子元素的JSON值（BLOB类型，通常显示为TEXT）。
• type: 子元素的类型（’object’, ‘array’, ‘string’, 等）。
• atom: 如果值是基本类型（string, number, boolean, null），返回其SQL表示；否则返回NULL。
• id: 子元素在其父节点中的唯一ID。
• parent: 父节点的ID。
• rowid: 与id相同。
• path: 子元素的JSON Path。
• fullkey: 包含根路径的完整JSON Path。

json_each() 示例：

“`sql
— 遍历第一个文档的根对象
SELECT key, value, type, atom
FROM json_each(
(SELECT doc_data FROM documents WHERE id = 1)
);
— 输出:
— key | value | type | atom
— ——— | ———– | ——- | ——
— name | “Alice” | string | Alice
— age | 30 | integer | 30
— isStudent | false | false | 0
— courses | [“Math”,… | array | NULL
— address | {“city”:… | object | NULL

— 遍历第一个文档的 ‘courses’ 数组
SELECT key, value, type, atom
FROM json_each(
(SELECT doc_data FROM documents WHERE id = 1),
‘$.courses’
);
— 输出:
— key | value | type | atom
— — | ——– | —— | —-
— 0 | “Math” | string | Math
— 1 | “Physics”| string | Physics

— 遍历第一个文档的 ‘address’ 对象
SELECT key, value, type, atom
FROM json_each(
(SELECT doc_data FROM documents WHERE id = 1),
‘$.address’
);
— 输出:
— key | value | type | atom
— —- | ———- | —— | —-
— city | “Wonderland”| string | Wonderland
— zip | “12345” | string | 12345

— 结合 json_each 和外部表查询（查找技能包含’Python’的文档ID）
SELECT T1.id, T1.doc_data, T2.value AS skill
FROM documents AS T1, json_each(T1.doc_data, ‘$.skills’) AS T2
WHERE T2.value = ‘Python’;
— 输出:
— id | doc_data | skill
— — | ——– | ——
— 2 | {…} | Python
“`

json_each 特别适用于遍历数组元素或对象属性，并经常与主表进行 JOIN （或更常见的，在 FROM 子句中列出，SQLite会进行交叉连接并智能过滤）来查找包含特定值的文档。

json_tree() 示例：

“`sql
— 递归遍历第一个文档的所有节点
SELECT path, key, value, type, atom
FROM json_tree(
(SELECT doc_data FROM documents WHERE id = 1)
);
— 输出 (部分，会非常详细):
— path | key | value | type | atom
— ——— | ——— | ———– | ——- | ——
— $ | NULL | {“name”:… | object | NULL
— $.name | name | “Alice” | string | Alice
— $.age | age | 30 | integer | 30
— $.isStudent| isStudent| false | false | 0
— $.courses | courses | [“Math”,… | array | NULL
— $.courses[0]| 0 | “Math” | string | Math
— $.courses[1]| 1 | “Physics” | string | Physics
— $.address | address | {“city”:… | object | NULL
— $.address.city| city | “Wonderland”| string | Wonderland
— $.address.zip | zip | “12345” | string | 12345

— 查找所有类型为 ‘string’ 且值包含 ‘Land’ 的节点
SELECT T1.id, T2.path, T2.value AS matched_value
FROM documents AS T1, json_tree(T1.doc_data) AS T2
WHERE T2.type = ‘string’ AND T2.value LIKE ‘%Land%’;
— 输出:
— id | path | matched_value
— — | ————– | ————-
— 1 | $.address.city | “Wonderland”
``json_tree在你需要查找JSON结构中任意位置的特定值或属性时非常强大，但相比json_each` 计算开销可能更大，因为它会遍历整个子树。

5. 修改 JSON 数据

SQLite提供了一组函数来修改JSON字符串。这些函数不会直接修改存储在表中的原始JSON字符串，而是返回一个新的修改后的JSON字符串。你需要使用 UPDATE 语句将返回的新字符串写回表中。

重要： 所有修改函数都以旧JSON字符串作为第一个参数，然后是一系列 path, value 对。path 指定要修改的位置，value 是要设置的新值。value 参数可以是任何SQL值，SQLite会将其适当地转换为JSON类型（字符串会加引号，数字、布尔、null保持原样，BLOB/TEXT如果看起来像JSON会被解析）。

5.1 json_insert(json, path1, value1, path2, value2, ...)

用途： 在指定的路径插入新的值。如果路径已经存在，json_insert 不会 覆盖它，而是忽略该 path, value 对。
返回值： 修改后的新JSON字符串。

“`sql
— 初始数据 (假设 id = 1)
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }

— 在根对象插入一个新的 key “city” 和一个已经存在的 key “age”
SELECT json_insert(doc_data, ‘$.city’, ‘Newcity’, ‘$.age’, 31)
FROM documents WHERE id = 1;
— 输出 (age没有被修改):
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” }, “city”: “Newcity” }

— 在 courses 数组的索引 1 处插入一个新课程 “Chemistry”
— 注意：这会插入到原索引 1 的位置，并将原索引 1 及之后的元素后移
SELECT json_insert(doc_data, ‘$.courses[1]’, ‘Chemistry’)
FROM documents WHERE id = 1;
— 输出:
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Math”, “Chemistry”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }

— 插入到数组末尾 (使用大于当前最大索引的索引)
SELECT json_insert(doc_data, ‘$.courses[99]’, ‘Art’) — 99 远大于当前索引
FROM documents WHERE id = 1;
— 输出:
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Math”, “Physics”, “Art”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }
“`

5.2 json_replace(json, path1, value1, path2, value2, ...)

用途： 替换指定路径的值。如果路径不存在，json_replace 不会 插入新的 path, value 对，而是忽略该对。
返回值： 修改后的新JSON字符串。

“`sql
— 初始数据 (假设 id = 1)
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }

— 替换 age 和一个不存在的 key “email”
SELECT json_replace(doc_data, ‘$.age’, 35, ‘$.email’, ‘[email protected]’)
FROM documents WHERE id = 1;
— 输出 (email没有被添加):
— { “name”: “Alice”, “age”: 35, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }

— 替换 courses 数组的索引 0 处的元素
SELECT json_replace(doc_data, ‘$.courses[0]’, ‘Calculus’)
FROM documents WHERE id = 1;
— 输出:
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Calculus”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }
“`

5.3 json_set(json, path1, value1, path2, value2, ...)

用途： 设置指定路径的值。如果路径存在，替换其值；如果路径不存在，插入新的 path, value 对。这是最常用的修改函数，因为它结合了 json_insert 和 json_replace 的行为。
返回值： 修改后的新JSON字符串。

“`sql
— 初始数据 (假设 id = 1)
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }

— 设置 age (替换) 和 email (插入)
SELECT json_set(doc_data, ‘$.age’, 35, ‘$.email’, ‘[email protected]’)
FROM documents WHERE id = 1;
— 输出:
— { “name”: “Alice”, “age”: 35, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” }, “email”: “[email protected]” }

— 设置 courses 数组的索引 0 处元素 (替换) 和索引 2 处元素 (插入到末尾)
SELECT json_set(doc_data, ‘$.courses[0]’, ‘Calculus’, ‘$.courses[2]’, ‘Art’)
FROM documents WHERE id = 1;
— 输出:
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Calculus”, “Physics”, “Art”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }

— 更新数据库中的数据
UPDATE documents
SET doc_data = json_set(doc_data, ‘$.age’, 35, ‘$.email’, ‘[email protected]’)
WHERE id = 1;

— 查看更新后的数据
SELECT doc_data FROM documents WHERE id = 1;
— 输出:
— {“name”:”Alice”,”age”:35,”isStudent”:false,”courses”:[“Math”,”Physics”],”address”:{“city”:”Wonderland”,”zip”:”12345″},”email”:”[email protected]”}
“`

5.4 json_remove(json, path1, path2, ...)

用途： 从JSON字符串中移除指定路径的元素。
返回值： 修改后的新JSON字符串。

“`sql
— 初始数据 (假设 id = 1)
— { “name”: “Alice”, “age”: 35, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” }, “email”: “[email protected]” }

— 移除 age 和 email 字段
SELECT json_remove(doc_data, ‘$.age’, ‘$.email’)
FROM documents WHERE id = 1;
— 输出:
— { “name”: “Alice”, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }

— 移除 courses 数组的索引 0 处的元素
SELECT json_remove(doc_data, ‘$.courses[0]’)
FROM documents WHERE id = 1;
— 输出:
— { “name”: “Alice”, “age”: 35, “isStudent”: false, “courses”: [“Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” }, “email”: “[email protected]” }
— 注意：移除数组元素后，后面的元素索引会向前移动。
“`

5.5 json_patch(json1, json2) (SQLite 3.38.0+)

用途： 根据 JSON Merge Patch 标准 (RFC 7396) 将 json2 应用到 json1 上。
返回值： 合并后的新JSON字符串。

JSON Merge Patch 规则简述：
* 如果 json2 是对象：递归合并其属性到 json1。
* json2 中存在的属性：如果 json1 中也存在，则替换；如果 json1 中不存在，则添加。
* json2 中属性的值是 null：如果 json1 中存在该属性，则删除该属性。
* 如果 json2 不是对象（字符串、数字、布尔、数组、null）：整个 json1 被 json2 替换。

“`sql
— 初始数据 (假设 id = 1)
— { “name”: “Alice”, “age”: 30, “isStudent”: false, “courses”: [“Math”, “Physics”], “address”: { “city”: “Wonderland”, “zip”: “12345” } }

— 应用一个合并补丁：更新age，添加email，删除isStudent
SELECT json_patch(
doc_data,
‘{ “age”: 31, “email”: “[email protected]”, “isStudent”: null }’
) FROM documents WHERE id = 1;
— 输出:
— {“name”:”Alice”,”age”:31,”courses”:[“Math”,”Physics”],”address”:{“city”:”Wonderland”,”zip”:”12345″},”email”:”[email protected]”}
— age 被更新, email 被添加, isStudent 被删除 (因为补丁中是 null)

— 应用一个合并补丁：替换整个 address 对象
SELECT json_patch(
doc_data,
‘{ “address”: { “city”: “Oz” } }’ — address 不是 null, 会替换整个对象
) FROM documents WHERE id = 1;
— 输出:
— {“name”:”Alice”,”age”:30,”isStudent”:false,”courses”:[“Math”,”Physics”],”address”:{“city”:”Oz”}}

— 应用一个非对象的补丁 (例如一个字符串)
SELECT json_patch(doc_data, ‘”New Data”‘);
— 输出: “New Data” — 整个原始JSON被替换
``json_patch` 对于应用部分更新非常方便，特别是当更新格式遵循 JSON Merge Patch 标准时。

6. 创建 JSON 数据

除了从已有文本解析和修改JSON，SQLite还可以从关系型数据中构建JSON结构。这在需要将查询结果以JSON格式返回给应用程序或API时非常有用。

6.1 json_array(value1, value2, ...)

用途： 从一组SQL值创建一个JSON数组。
返回值： JSON数组字符串 (BLOB类型)。

sql
SELECT json_array('apple', 123, true, null, json_object('x', 1, 'y', 2));
-- 输出: ["apple",123,true,null,{"x":1,"y":2}]
json_array 的每个参数都会成为数组的一个元素。SQL NULL会转换为JSON null。

6.2 json_object(key1, value1, key2, value2, ...)

用途： 从一系列键值对创建一个JSON对象。键必须是字符串。
返回值： JSON对象字符串 (BLOB类型)。

“`sql
SELECT json_object(‘name’, ‘Bob’, ‘age’, 25, ‘isActive’, false);
— 输出: {“name”:”Bob”,”age”:25,”isActive”:false}

SELECT json_object(
‘id’, 10,
‘data’, json_object(‘type’, ‘widget’, ‘value’, 99),
‘tags’, json_array(‘a’, ‘b’, ‘c’)
);
— 输出: {“id”:10,”data”:{“type”:”widget”,”value”:99},”tags”:[“a”,”b”,”c”]}
“`
键值对必须成对出现。键必须是字符串（或能被转换为字符串的SQL值）。

6.3 json_group_array(value) (聚合函数)

用途： 在 GROUP BY 操作中，将分组内的所有值聚合到一个JSON数组中。
返回值： JSON数组字符串 (BLOB类型)。

“`sql
— 假设有另一个表 orders
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
item_name TEXT,
price REAL
);

INSERT INTO orders (customer_id, item_name, price) VALUES
(1, ‘Laptop’, 1200.00),
(1, ‘Keyboard’, 75.00),
(2, ‘Mouse’, 25.00),
(1, ‘Monitor’, 300.00),
(2, ‘Webcam’, 50.00);

— 按客户ID分组，并将每个客户的订单项聚合成一个JSON数组
SELECT
customer_id,
json_group_array(item_name) AS items_list
FROM orders
GROUP BY customer_id;
— 输出:
— customer_id | items_list
— ———– | ————————
— 1 | [“Laptop”,”Keyboard”,”Monitor”]
— 2 | [“Mouse”,”Webcam”]

— 聚合整个订单对象
SELECT
customer_id,
json_group_array(json_object(‘item’, item_name, ‘cost’, price)) AS order_details
FROM orders
GROUP BY customer_id;
— 输出:
— customer_id | order_details
— ———– | —————————————————————
— 1 | [{“item”:”Laptop”,”cost”:1200.0},{“item”:”Keyboard”,”cost”:75.0},{“item”:”Monitor”,”cost”:300.0}]
— 2 | [{“item”:”Mouse”,”cost”:25.0},{“item”:”Webcam”,”cost”:50.0}]
“`

6.4 json_group_object(key, value) (聚合函数)

用途： 在 GROUP BY 操作中，将分组内的键值对聚合成一个JSON对象。
返回值： JSON对象字符串 (BLOB类型)。注意，如果同一个键在分组内出现多次，行为是未定义的或取决于SQLite版本和实现（通常是最后一个值获胜）。此函数更适用于键是唯一的场景。

“`sql
— 创建一个包含用户元数据的表
CREATE TABLE user_meta (
user_id INTEGER,
meta_key TEXT,
meta_value TEXT,
PRIMARY KEY (user_id, meta_key)
);

INSERT INTO user_meta (user_id, meta_key, meta_value) VALUES
(1, ’email’, ‘[email protected]’),
(1, ‘city’, ‘Wonderland’),
(2, ’email’, ‘[email protected]’),
(2, ‘occupation’, ‘Engineer’);

— 按用户ID分组，将元数据聚合成一个JSON对象
SELECT
user_id,
json_group_object(meta_key, meta_value) AS metadata
FROM user_meta
GROUP BY user_id;
— 输出:
— user_id | metadata
— ——- | —————————————–
— 1 | {“email”:”[email protected]”,”city”:”Wonderland”}
— 2 | {“email”:”[email protected]”,”occupation”:”Engineer”}
“`

这些聚合函数 (json_group_array, json_group_object) 是SQLite中将关系型数据转换为嵌套JSON结构的核心工具。

7. 索引 JSON 数据

直接在 TEXT 列上创建索引对于基于JSON内容的查询没有帮助，因为索引存储的是完整的字符串，无法加速对JSON内部特定路径值的查找。

为了加速基于JSON路径的查询，你可以使用 生成列 (Generated Columns)，并在这些生成列上创建索引。生成列的值是根据表中其他列计算得出的，可以是虚拟的（每次读取时计算）或存储的（计算后存储在磁盘上）。

版本要求： 生成列在 SQLite 3.31.0 及更高版本中支持。

示例：

假设我们经常需要按 name 和 age 字段查询或排序 documents 表。我们可以创建生成列来提取这些值：

“`sql
— 添加生成列来提取 name 和 age
ALTER TABLE documents ADD COLUMN person_name TEXT AS (json_extract(doc_data, ‘$.name’)) VIRTUAL;
ALTER TABLE documents ADD COLUMN person_age INTEGER AS (json_extract(doc_data, ‘$.age’)) VIRTUAL;

— VIRTUAL 列不占用额外存储空间，但在查询时会计算。
— 如果希望值被物理存储以加快读取速度（但会增加存储空间和写入开销），可以使用 STORED 关键字代替 VIRTUAL。
— ALTER TABLE documents ADD COLUMN person_name TEXT AS (json_extract(doc_data, ‘$.name’)) STORED;
— ALTER TABLE documents ADD COLUMN person_age INTEGER AS (json_extract(doc_data, ‘$.age’)) STORED;
— 注意：SQLite目前只支持 VIRTUAL 生成列，STORED 尚未实现。

— 在生成列上创建索引
CREATE INDEX idx_documents_person_name ON documents (person_name);
CREATE INDEX idx_documents_person_age ON documents (person_age);
“`

现在，当执行以下查询时，SQLite 可以利用在 person_name 和 person_age 列上的索引，显著提高性能：

“`sql
— 查询将使用 idx_documents_person_age 索引
SELECT id, person_name, person_age
FROM documents
WHERE person_age < 35;

— 查询将使用 idx_documents_person_name 索引
SELECT id, person_name, person_age
FROM documents
WHERE person_name = ‘Alice’;

— 排序也可能受益于索引
SELECT id, person_name, person_age
FROM documents
ORDER BY person_name;
“`

通过 EXPLAIN QUERY PLAN 查看是否使用了索引：
sql
EXPLAIN QUERY PLAN
SELECT id, person_name, person_age
FROM documents
WHERE person_age < 35;
-- 输出应该显示使用了 idx_documents_person_age 索引

限制：
* 生成列只能基于同一行中其他列的确定性函数创建。JSON函数通常是确定性的。
* 每个需要索引的JSON路径都需要一个单独的生成列和索引。这可能会导致列的数量膨胀，并需要提前规划好哪些JSON路径是主要的查询目标。
* 对于动态的、未知结构的JSON，生成列索引可能不适用。

对于需要频繁查询的特定JSON路径，生成列和索引是提升性能的关键。

8. 性能考虑与最佳实践

• JSON vs. 关系型： JSON的灵活性是以牺牲结构化数据的某些优势为代价的。对于固定且简单的属性，最好还是使用标准的列。仅在属性数量多变、结构嵌套或不确定时考虑使用JSON列。
• 验证 JSON： 确保存储在 TEXT 列中的数据是有效的JSON。可以在应用程序层验证，或考虑使用触发器甚至 CHECK 约束（如果性能允许）。
• 索引重要路径： 对于频繁用于过滤、连接或排序的JSON路径，使用生成列并创建索引。这是提高JSON查询性能最重要的手段。
• 避免全表扫描和全JSON解析： 没有索引的情况下，对JSON内容的查询通常需要读取并解析整个JSON字符串，即使只需要其中的一小部分。生成列可以避免重复解析。
• 选择合适的函数： json_extract 适用于获取特定值；json_each 和 json_tree 适用于遍历和查找嵌套数据；json_set 是常用的修改函数；聚合函数用于从关系数据生成JSON。
• JSON Path 精度： 确保使用的JSON Path准确无误。错误的路径会返回 NULL。
• 类型转换： json_extract 返回的值是TEXT或BLOB。在进行数值或布尔比较时，SQLite通常会进行隐式转换，但在某些复杂情况下或为了明确起见，可能需要使用 CAST 或类型转换函数（如 CAST(json_extract(doc_data, '$.age') AS INTEGER)）。
• 处理 NULL： JSON null 值在SQLite中会转换为 SQL NULL。使用 IS NULL 或 IS NOT NULL 进行判断。路径不存在也会导致 json_extract 返回 SQL NULL。

9. 总结

SQLite的JSON功能强大且灵活，为处理应用程序中的非结构化或半结构化数据提供了一种便捷的方式。通过 json_valid 验证，json_extract 读取，json_insert, json_replace, json_set, json_remove, json_patch 修改，以及 json_array, json_object, json_group_array, json_group_object 创建，你可以直接在数据库层面高效地操作JSON数据。结合生成列和索引，还可以显著提升对JSON内部特定路径的查询性能。

了解并熟练使用这些SQLite JSON函数，可以帮助你更好地设计数据库模式，简化应用程序逻辑，并提高数据处理的效率。在权衡数据结构时，JSON列是传统关系型列的一个有力补充，特别适用于那些模式经常变化、包含复杂嵌套结构或属性稀疏的场景。

---