SQLite JSON支持 #

-- SQLite 3.38+ 内置 JSON 支持
-- 之前版本需要加载扩展

-- JSON 数据存储为 TEXT 类型
CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    data TEXT  -- JSON存储为TEXT
);

-- 检查版本
SELECT sqlite_version();

-- SQLite JSON 函数：
-- json()          - 验证并返回JSON
-- json_array()    - 创建JSON数组
-- json_object()   - 创建JSON对象
-- json_extract()  - 提取JSON值
-- json_set()      - 设置JSON值
-- json_insert()   - 插入JSON值
-- json_replace()  - 替换JSON值
-- json_remove()   - 删除JSON值
-- json_type()     - 获取JSON类型
-- json_valid()    - 验证JSON有效性
-- json_each()     - 遍历JSON
-- json_tree()     - 递归遍历JSON

-- 创建JSON对象
SELECT json_object('name', 'Alice', 'age', 25);
-- {"name":"Alice","age":25}

-- 嵌套对象
SELECT json_object(
    'name', 'Alice',
    'address', json_object('city', 'Beijing', 'zip', '100000')
);
-- {"name":"Alice","address":{"city":"Beijing","zip":"100000"}}

-- 包含数组
SELECT json_object(
    'name', 'Alice',
    'hobbies', json_array('reading', 'swimming')
);
-- {"name":"Alice","hobbies":["reading","swimming"]}

-- 创建JSON数组
SELECT json_array(1, 2, 3, 4, 5);
-- [1,2,3,4,5]

-- 创建空数组
SELECT json_array();
-- []

-- 混合类型数组
SELECT json_array('Alice', 25, true, null);
-- ["Alice",25,true,null]

-- 嵌套数组
SELECT json_array(1, json_array(2, 3), 4);
-- [1,[2,3],4]

-- 验证并返回JSON
SELECT json('{"name":"Alice"}');
-- {"name":"Alice"}

-- 格式化JSON
SELECT json('{"name":"Alice","age":25}');
-- {"name":"Alice","age":25}

-- 提取对象属性
SELECT json_extract('{"name":"Alice","age":25}', '$.name');
-- 'Alice'

SELECT json_extract('{"name":"Alice","age":25}', '$.age');
-- 25

-- 提取嵌套属性
SELECT json_extract(
    '{"name":"Alice","address":{"city":"Beijing"}}',
    '$.address.city'
);
-- 'Beijing'

-- 提取数组元素
SELECT json_extract('["a","b","c"]', '$[0]');
-- 'a'

SELECT json_extract('["a","b","c"]', '$[1]');
-- 'b'

-- 提取数组切片
SELECT json_extract('["a","b","c","d"]', '$[1:3]');
-- ["b","c"]

-- 提取多个值
SELECT json_extract('{"name":"Alice","age":25}', '$.name', '$.age');
-- 'Alice|25'

-- 创建表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    attributes TEXT  -- JSON数据
);

INSERT INTO users (name, attributes) VALUES
    ('Alice', '{"age":25,"city":"Beijing","hobbies":["reading","swimming"]}'),
    ('Bob', '{"age":30,"city":"Shanghai","hobbies":["gaming"]}');

-- 提取属性
SELECT 
    name,
    json_extract(attributes, '$.age') AS age,
    json_extract(attributes, '$.city') AS city
FROM users;

-- 提取数组元素
SELECT 
    name,
    json_extract(attributes, '$.hobbies[0]') AS first_hobby
FROM users;

-- 在WHERE中使用
SELECT name FROM users 
WHERE json_extract(attributes, '$.age') > 25;

-- SQLite 3.38+ 支持 -> 和 ->> 操作符

-- -> 返回JSON类型
SELECT attributes -> '$.age' FROM users;
-- 25 (JSON)

-- ->> 返回SQL类型
SELECT attributes ->> '$.age' FROM users;
-- 25 (INTEGER)

-- 数组访问
SELECT attributes -> '$.hobbies[0]' FROM users;
SELECT attributes ->> '$.hobbies[0]' FROM users;

-- 设置JSON值（存在则更新，不存在则插入）
SELECT json_set('{"name":"Alice"}', '$.age', 25);
-- {"name":"Alice","age":25}

SELECT json_set('{"name":"Alice","age":20}', '$.age', 25);
-- {"name":"Alice","age":25}

-- 设置嵌套值
SELECT json_set(
    '{"name":"Alice"}',
    '$.address.city', 'Beijing'
);
-- {"name":"Alice","address":{"city":"Beijing"}}

-- 在表中更新
UPDATE users 
SET attributes = json_set(attributes, '$.age', 26)
WHERE name = 'Alice';

-- 插入JSON值（只插入不存在的）
SELECT json_insert('{"name":"Alice"}', '$.age', 25);
-- {"name":"Alice","age":25}

SELECT json_insert('{"name":"Alice","age":20}', '$.age', 25);
-- {"name":"Alice","age":20}  -- 不更新

-- 替换JSON值（只替换存在的）
SELECT json_replace('{"name":"Alice"}', '$.age', 25);
-- {"name":"Alice"}  -- 不插入

SELECT json_replace('{"name":"Alice","age":20}', '$.age', 25);
-- {"name":"Alice","age":25}  -- 更新

-- 删除JSON值
SELECT json_remove('{"name":"Alice","age":25}', '$.age');
-- {"name":"Alice"}

-- 删除多个值
SELECT json_remove('{"a":1,"b":2,"c":3}', '$.a', '$.c');
-- {"b":2}

-- 删除数组元素
SELECT json_remove('["a","b","c"]', '$[1]');
-- ["a","c"]

-- 在表中删除
UPDATE users 
SET attributes = json_remove(attributes, '$.age')
WHERE name = 'Alice';

-- 获取JSON类型
SELECT json_type('{"name":"Alice"}');        -- 'object'
SELECT json_type('["a","b","c"]');           -- 'array'
SELECT json_type('"hello"');                 -- 'text'
SELECT json_type('123');                     -- 'integer'
SELECT json_type('3.14');                    -- 'real'
SELECT json_type('true');                    -- 'true'
SELECT json_type('null');                    -- 'null'

-- 获取指定路径的类型
SELECT json_type('{"name":"Alice","age":25}', '$.name');  -- 'text'
SELECT json_type('{"name":"Alice","age":25}', '$.age');   -- 'integer'

-- 验证JSON有效性
SELECT json_valid('{"name":"Alice"}');  -- 1 (有效)
SELECT json_valid('{name:Alice}');      -- 0 (无效)

-- 在约束中使用
CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    data TEXT CHECK(json_valid(data))
);

-- 获取数组长度
SELECT json_array_length('["a","b","c"]');  -- 3
SELECT json_array_length('[]');              -- 0

-- 获取嵌套数组长度
SELECT json_array_length('{"items":["a","b","c"]}', '$.items');  -- 3

-- 遍历JSON对象
SELECT * FROM json_each('{"name":"Alice","age":25}');
-- key    | value | type
-- --------|-------|-------
-- name   | Alice | text
-- age    | 25    | integer

-- 遍历JSON数组
SELECT * FROM json_each('["a","b","c"]');
-- key | value | type
-- ----|-------|-------
-- 0   | a     | text
-- 1   | b     | text
-- 2   | c     | text

-- 在查询中使用
SELECT 
    users.name,
    hobbies.value AS hobby
FROM users,
     json_each(json_extract(attributes, '$.hobbies')) AS hobbies;

-- 递归遍历JSON
SELECT * FROM json_tree('{"name":"Alice","address":{"city":"Beijing"}}');
-- 递归显示所有节点

-- 查找特定值
SELECT * FROM json_tree('{"a":1,"b":{"c":2}}') 
WHERE value = 2;

-- 将多行聚合为JSON数组
SELECT json_group_array(name) FROM users;
-- ["Alice","Bob"]

-- 分组聚合
SELECT 
    department,
    json_group_array(name) AS employees
FROM employees
GROUP BY department;

-- 将多行聚合为JSON对象
SELECT json_group_object(name, age) FROM users;
-- {"Alice":25,"Bob":30}

-- 存储应用配置
CREATE TABLE app_config (
    key TEXT PRIMARY KEY,
    value TEXT
);

INSERT INTO app_config VALUES 
    ('theme', '{"mode":"dark","color":"blue"}'),
    ('notifications', '{"email":true,"sms":false}');

-- 读取配置
SELECT json_extract(value, '$.mode') FROM app_config WHERE key = 'theme';

-- 更新配置
UPDATE app_config 
SET value = json_set(value, '$.mode', 'light')
WHERE key = 'theme';

-- 存储日志数据
CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    data TEXT
);

INSERT INTO logs (data) VALUES 
    (json_object('level', 'INFO', 'message', 'User logged in', 'user_id', 123));

-- 查询日志
SELECT * FROM logs 
WHERE json_extract(data, '$.level') = 'ERROR';

-- 存储文章标签
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    tags TEXT  -- JSON数组
);

INSERT INTO articles (title, tags) VALUES
    ('SQLite Guide', json_array('database', 'sql', 'tutorial')),
    ('Python Tips', json_array('python', 'programming'));

-- 查找包含特定标签的文章
SELECT title FROM articles 
WHERE EXISTS (
    SELECT 1 FROM json_each(tags) WHERE value = 'sql'
);