数据库原理与选型 / 系统课时

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  12

JSON 路径以 $ 表示根节点,点号用于访问对象属性,数组下标使用方括号。不存在的路径通常返回 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 字段已成为核心查询条件,也可以考虑改为普通列或生成列。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

JSON 数据的存储与查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通