从零开始学习 SQLite JSON 数据操作

SQLite 从 3.9 版本开始引入了对 JSON 数据的原生支持，这极大地扩展了 SQLite 的应用范围，使得在移动端和嵌入式系统中处理结构化数据变得更加便捷。本文将从零开始，详细介绍如何在 SQLite 中进行 JSON 数据的操作，涵盖从基础概念到高级应用的各个方面。

一、 JSON 数据类型与基本操作

SQLite 使用 TEXT 数据类型存储 JSON 字符串。虽然没有专门的 JSON 数据类型，但 SQLite 提供了一系列强大的 JSON 函数来解析和操作这些字符串。

创建包含 JSON 数据的表:

sql CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT, details TEXT -- 存储 JSON 格式的产品详情 );

插入 JSON 数据:

sql INSERT INTO products (name, details) VALUES ( 'Product A', '{"color": "red", "size": "L", "price": 25.99}' );

查询 JSON 数据:

sql SELECT details FROM products WHERE id = 1;

这将返回包含 JSON 字符串的 details 列。

二、提取 JSON 数据：json_extract() 函数

json_extract(json_string, path) 函数是提取 JSON 数据的核心工具。它使用类似 XPath 的路径表达式来定位 JSON 对象中的特定值。

路径表达式: 路径表达式以 $ 符号开头，表示 JSON 文档的根节点。使用点号 . 访问对象成员，使用方括号 [] 访问数组元素。

sql SELECT json_extract(details, '$.color') AS color FROM products WHERE id = 1; -- 返回 "red" SELECT json_extract(details, '$.size') AS size FROM products WHERE id = 1; -- 返回 "L" SELECT json_extract(details, '$.price') AS price FROM products WHERE id = 1; -- 返回 25.99

数组索引: 可以使用数字索引访问数组元素，索引从 0 开始。

sql -- 假设 details 为 '{"colors": ["red", "green", "blue"]}' SELECT json_extract(details, '$.colors[0]') AS first_color FROM products WHERE id = 1; -- 返回 "red"

多值提取: 可以同时提取多个值。

sql SELECT json_extract(details, '$.color', '$.size') FROM products WHERE id = 1; -- 返回 "red" "L"

三、 JSON 对象操作:

json_object(): 创建 JSON 对象。

sql SELECT json_object('name', 'Product B', 'price', 30.50); -- 返回 '{"name": "Product B", "price": 30.50}'

json_insert(): 向 JSON 对象插入或替换值。

sql -- 假设 details 为 '{"color": "red"}' SELECT json_insert(details, '$.size', 'M') FROM products WHERE id = 1; -- 返回 '{"color": "red", "size": "M"}' SELECT json_insert(details, '$.color', 'blue') FROM products WHERE id = 1;-- 返回 '{"color": "blue"}' (替换原值)

json_replace(): 替换 JSON 对象中的值，仅当路径已存在时才替换.

sql -- 假设 details 为 '{"color": "red"}' SELECT json_replace(details, '$.color', 'blue') FROM products WHERE id = 1;-- 返回 '{"color": "blue"}' SELECT json_replace(details, '$.size', 'M') FROM products WHERE id = 1; -- 返回 '{"color": "red"}' (size 不存在，不替换)

json_remove(): 删除 JSON 对象中的值。

sql -- 假设 details 为 '{"color": "red", "size": "M"}' SELECT json_remove(details, '$.size') FROM products WHERE id = 1; -- 返回 '{"color": "red"}'

json_set(): 插入或替换 JSON 对象中的值，如果路径中的部分不存在，则创建它们。

sql -- 假设 details 为 '{"color": "red"}' SELECT json_set(details, '$.dimensions.height', 10, '$.dimensions.width', 20) FROM products WHERE id = 1; -- 返回 '{"color": "red", "dimensions": {"height": 10, "width": 20}}'

四、 JSON 数组操作:

json_array(): 创建 JSON 数组。

sql SELECT json_array('red', 'green', 'blue'); -- 返回 '["red", "green", "blue"]'

json_insert(): 在指定位置插入元素到 JSON 数组。

sql -- 假设 details 为 '["red", "green"]' SELECT json_insert(details, '$[1]', 'blue') FROM products WHERE id = 1; -- 返回 '["red", "blue", "green"]' SELECT json_insert(details, '$[2]', 'yellow') FROM products WHERE id = 1; -- 返回 '["red", "green", "yellow"]' (追加到末尾)

json_replace(): 替换 JSON 数组中的元素.

sql -- 假设 details 为 '["red", "green"]' SELECT json_replace(details, '$[0]', 'blue') FROM products WHERE id = 1; -- 返回 '["blue", "green"]'

json_remove(): 删除 JSON 数组中的元素.

sql -- 假设 details 为 '["red", "green", "blue"]' SELECT json_remove(details, '$[1]') FROM products WHERE id = 1; -- 返回 '["red", "blue"]'

五、查询和过滤 JSON 数据：json_valid()， json_each() 和 WHERE 子句

json_valid(): 检查 JSON 字符串是否有效。

sql SELECT id FROM products WHERE json_valid(details); -- 返回有效 JSON 数据的 id

json_each(): 将 JSON 数组或对象展开成多行。

“`sql
— 假设 details 为 ‘{“colors”: [“red”, “green”], “sizes”: [“S”, “M”]}’
SELECT json_each.key, json_each.value
FROM products, json_each(details)
WHERE id = 1;
— 返回多行结果:
— key | value
— ——–|——-
— colors | [“red”, “green”]
— sizes | [“S”, “M”]

SELECT json_each.value
FROM products, json_each(json_extract(details, ‘$.colors’))
WHERE id = 1;
— 返回多行结果:
— value

— red
— green

“`

WHERE 子句与 json_extract(): 可以使用 json_extract() 在 WHERE 子句中过滤数据.

sql SELECT * FROM products WHERE json_extract(details, '$.color') = 'red'; -- 查找颜色为红色的产品 SELECT * FROM products WHERE json_extract(details, '$.price') > 20; -- 查找价格大于 20 的产品

六、其他 JSON 函数

json_type(): 返回 JSON 值的类型 (例如 “object”, “array”, “integer”, “string”).
json_array_length(): 返回 JSON 数组的长度.
json_patch(): 将一个 JSON 对象应用于另一个 JSON 对象作为补丁.
json_quote(): 将文本值转换成 JSON 字符串，包括必要的转义.
json_group_array() / json_group_object(): 用于聚合函数中，将多个值聚合为 JSON 数组或对象.

七、实践案例：构建一个简单的产品目录

假设我们需要构建一个简单的产品目录，其中每个产品包含名称、描述、价格和一系列图片 URL。

创建表:

sql CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT, description TEXT, price REAL, images TEXT -- 存储 JSON 格式的图片 URL 数组 );

插入数据:

“`sql
INSERT INTO products (name, description, price, images) VALUES (
‘T-Shirt’,
‘Comfortable cotton T-shirt’,
19.99,
‘[“image1.jpg”, “image2.jpg”, “image3.jpg”]’
);

“`

查询和过滤:

“`sql
SELECT * FROM products WHERE json_extract(images, ‘$[0]’) = ‘image1.jpg’;

SELECT name, json_extract(images, ‘$[1]’) AS second_image
FROM products WHERE price < 25;

SELECT id, value FROM products, json_each(images) WHERE name = ‘T-Shirt’;
“`

八、总结

SQLite 的 JSON 支持为在应用程序中高效地存储和查询结构化数据提供了强大的工具。通过熟练掌握本文介绍的 JSON 函数和操作技巧，你可以充分利用 SQLite 的灵活性，构建更加强大和功能丰富的应用程序。希望这篇文章能帮助你从零开始学习 SQLite JSON 数据操作，并将其应用于你的实际项目中.

从零开始学习 SQLite JSON 数据操作

发表评论 取消回复

发表评论取消回复