数据库原理与选型 / 系统课时
JSON 数据的存储与查询
JSON 数据的存储与查询
本课解决“结构灵活的数据不适合拆成大量固定列”的问题。完成后,你可以验证 JSON 文本、读取嵌套字段、局部更新内容,并把数组元素展开成可查询的行。
学习目标
- 使用
json与json_extract读取 JSON - 使用
json_set修改指定路径 - 使用
json_each展开数组或对象
存储并读取 JSON
SQLite 通常把 JSON 保存为 TEXT。json() 会验证并规范化输入,json_extract() 按路径读取字段。
sql
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
attributes TEXT NOT NULL
CHECK (json_valid(attributes))
);
INSERT INTO products (id, name, attributes) VALUES (
1,
'机械键盘',
json('{"brand":"KeyPro","price":399,"tags":["有线","机械轴"],"stock":{"shanghai":12,"beijing":8}}')
);
SELECT
name,
json_extract(attributes, '$.brand') AS brand,
json_extract(attributes, '$.stock.shanghai') AS shanghai_stock
FROM products;预期输出
text
name brand shanghai_stock
-------- ------ --------------
机械键盘 KeyPro 12JSON 路径以 $ 表示根节点,点号用于访问对象属性,数组下标使用方括号。不存在的路径通常返回 NULL。
局部修改 JSON
json_set 会设置指定路径:路径存在时替换,不存在时创建。下面更新北京库存并新增颜色字段。
sql
UPDATE products
SET attributes = json_set(
attributes,
'$.stock.beijing', 10,
'$.color', '黑色'
)
WHERE id = 1;
SELECT json_extract(attributes, '$.stock.beijing') AS beijing_stock,
json_extract(attributes, '$.color') AS color
FROM products
WHERE id = 1;预期输出
text
beijing_stock color
------------- -----
10 黑色更新的是整个 JSON 文本值,因此高频修改、需要严格约束或经常参与连接的字段,仍更适合设计成普通列。
使用 json_each 展开数组
json_each 是表值函数,可以把数组的每个元素转换为一行,再参与筛选、连接和聚合。
sql
SELECT
p.name,
j.value AS tag
FROM products AS p,
json_each(p.attributes, '$.tags') AS j
ORDER BY j.key;预期输出
text
name tag
-------- ------
机械键盘 有线
机械键盘 机械轴这里的 j.key 是数组下标,j.value 是元素值。展开对象时,key 对应属性名。
建立可索引的查询入口
直接按 JSON 路径筛选时,普通索引无法自动识别表达式。可以为常用路径建立表达式索引。
sql
CREATE INDEX idx_products_brand
ON products (json_extract(attributes, '$.brand'));
EXPLAIN QUERY PLAN
SELECT id, name
FROM products
WHERE json_extract(attributes, '$.brand') = 'KeyPro';预期输出
text
查询计划中出现 USING INDEX idx_products_brand。查询计划的编号和完整文字可能随版本变化,应重点确认是否使用了目标索引。若某个 JSON 字段已成为核心查询条件,也可以考虑改为普通列或生成列。
按自己的节奏,确认真正理解。