-- json_object()：键值对创建对象
SELECT json_object('name', '张三', 'age', 25, 'active', json(1));
-- {"name":"张三","age":25,"active":true}

-- json_array()：创建数组
SELECT json_array(1, 'hello', NULL, 3.14, json('true'));
-- [1,"hello",null,3.14,true]

-- json()：验证并规范化
SELECT json('{"name":"张三","age":25}');
-- {"name":"张三","age":25}

-- 嵌套结构
SELECT json_object(
    'name', '张三',
    'address', json_object('city', '北京', 'zip', '100000'),
    'hobbies', json_array('阅读', '编程', '旅行')
);
-- {"name":"张三","address":{"city":"北京","zip":"100000"},"hobbies":["阅读","编程","旅行"]}

-- json_quote()：将值转为 JSON 字面量
SELECT json_quote('hello');   -- "hello"
SELECT json_quote(42);        -- 42
SELECT json_quote(NULL);      -- null

SET @data = '{"name":"张三","age":25,"address":{"city":"北京"},"hobbies":["阅读","编程"]}';

-- 基本提取
SELECT json_extract('{"name":"张三","age":25}', '$.name');  -- 张三
SELECT json_extract('{"name":"张三","age":25}', '$.age');   -- 25

-- 嵌套路径
SELECT json_extract('{"address":{"city":"北京"}}', '$.address.city');  -- 北京

-- 数组索引
SELECT json_extract('["a","b","c"]', '$[0]');  -- a
SELECT json_extract('["a","b","c"]', '$[2]');  -- c
SELECT json_extract('["a","b","c"]', '$[#-1]');  -- c（最后一个）

-- 多路径提取（返回数组）
SELECT json_extract('{"a":1,"b":2,"c":3}', '$.a', '$.c');  -- [1,3]

-- 通配符（数组）
SELECT json_extract('[1,2,3,4]', '$[*]');  -- [1,2,3,4]

-- 路径语法
-- $.key        — 对象成员
-- $[0]         — 数组元素
-- $[#]         — 数组长度
-- $[#-1]       — 最后一个元素
-- $.a.b.c      — 嵌套路径
-- $[*]         — 所有元素
-- $.key[*]     — 对象成员的所有子元素

-- -> 返回 JSON 值（保留类型和引号）
SELECT '{"name":"张三","age":25}' -> '$.name';   -- "张三"
SELECT '{"name":"张三","age":25}' -> '$.age';    -- 25

-- ->> 返回 SQL 值（去除引号）
SELECT '{"name":"张三","age":25}' ->> '$.name';  -- 张三
SELECT '{"name":"张三","age":25}' ->> '$.age';   -- 25

-- 实际使用对比
-- -> 适合嵌套 JSON 或继续使用 json 函数
-- ->> 适合直接比较和显示
SELECT * FROM products
WHERE info ->> '$.category' = '电子产品'
AND (info ->> '$.price')::REAL > 100;

-- json_set()：设置值（不存在则创建，存在则更新）
SELECT json_set('{"a":1}', '$.b', 2);         -- {"a":1,"b":2}
SELECT json_set('{"a":1}', '$.a', 99);         -- {"a":99}
SELECT json_set('{}', '$.name', '张三', '$.age', 25);  -- {"name":"张三","age":25}

-- json_insert()：仅在路径不存在时插入
SELECT json_insert('{"a":1}', '$.b', 2);       -- {"a":1,"b":2}
SELECT json_insert('{"a":1}', '$.a', 99);       -- {"a":1}（已存在，不修改）

-- json_replace()：仅在路径存在时替换
SELECT json_replace('{"a":1}', '$.b', 2);      -- {"a":1}（不存在，不创建）
SELECT json_replace('{"a":1}', '$.a', 99);      -- {"a":99}

-- json_remove()：删除路径
SELECT json_remove('{"a":1,"b":2,"c":3}', '$.b');  -- {"a":1,"c":3}
SELECT json_remove('["a","b","c"]', '$[1]');        -- ["a","c"]

-- json_patch()：合并 JSON 对象
SELECT json_patch('{"a":1,"b":2}', '{"b":3,"c":4}');  -- {"a":1,"b":3,"c":4}
-- 第二个对象覆盖第一个的同名键

-- 展开 JSON 数组
SELECT * FROM json_each('["苹果","香蕉","橙子"]');
-- key  value  type  atom  id  parent  fullkey  path
-- 0    苹果   text  苹果  1   0       $[0]     $
-- 1    香蕉   text  香蕉  3   0       $[1]     $
-- 2    橙子   text  橙子  5   0       $[2]     $

-- 展开 JSON 对象
SELECT * FROM json_each('{"name":"张三","age":25}');
-- key    value  type     atom  id  parent  fullkey  path
-- name   张三   text     张三  1   0       $.name   $
-- age    25     integer  25    3   0       $.age    $

-- 实际使用：数组转行
SELECT
    key AS idx,
    value AS item
FROM json_each('["苹果","香蕉","橙子"]');

-- 展开嵌套数组
CREATE TABLE orders (id INTEGER PRIMARY KEY, items TEXT);
INSERT INTO orders VALUES (1, '[{"product":"苹果","qty":2},{"product":"香蕉","qty":3}]');

SELECT
    o.id,
    je.value ->> '$.product' AS product,
    je.value ->> '$.qty' AS quantity
FROM orders o, json_each(o.items) je;

-- 递归展开嵌套 JSON
SELECT * FROM json_tree('{"a":{"b":1},"c":[2,3]}');

-- 只查看特定路径的子树
SELECT * FROM json_tree('{"a":{"b":{"c":1}}}', '$.a');

-- json_group_array()：将行聚合为 JSON 数组
CREATE TABLE tags (article_id INTEGER, tag TEXT);
INSERT INTO tags VALUES (1, 'SQLite'), (1, '数据库'), (1, '教程');
INSERT INTO tags VALUES (2, 'Python'), (2, '编程');

SELECT article_id, json_group_array(tag) AS tags
FROM tags
GROUP BY article_id;
-- 1  ["SQLite","数据库","教程"]
-- 2  ["Python","编程"]

-- json_group_object()：将行聚合为 JSON 对象
CREATE TABLE attrs (item_id TEXT, key TEXT, value TEXT);
INSERT INTO attrs VALUES ('item1', 'color', 'red');
INSERT INTO attrs VALUES ('item1', 'size', 'large');
INSERT INTO attrs VALUES ('item2', 'color', 'blue');

SELECT item_id, json_group_object(key, value) AS attrs
FROM attrs
GROUP BY item_id;
-- item1  {"color":"red","size":"large"}
-- item2  {"color":"blue"}

-- 组合使用
SELECT json_object(
    'id', id,
    'name', name,
    'tags', (SELECT json_group_array(tag) FROM tags WHERE article_id = a.id)
) AS article_json
FROM articles a;

-- 创建存储 JSON 的表
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    data TEXT NOT NULL  -- JSON 字符串
);

INSERT INTO products VALUES
    (1, '{"name":"iPhone","category":"电子产品","price":5999}'),
    (2, '{"name":"MacBook","category":"电子产品","price":12999}'),
    (3, '{"name":"T恤","category":"服装","price":99}');

-- 为 JSON 字段创建索引
CREATE INDEX idx_products_category ON products(json_extract(data, '$.category'));
CREATE INDEX idx_products_price ON products(json_extract(data, '$.price'));

-- 使用索引查询
EXPLAIN QUERY PLAN
SELECT * FROM products
WHERE json_extract(data, '$.category') = '电子产品';
-- SEARCH TABLE products USING INDEX idx_products_category

-- 复合表达式索引
CREATE INDEX idx_products_cat_price ON products(
    json_extract(data, '$.category'),
    json_extract(data, '$.price')
);

-- 只为电子产品创建价格索引
CREATE INDEX idx_electronics_price ON products(
    json_extract(data, '$.price')
) WHERE json_extract(data, '$.category') = '电子产品';

-- 使用 ->> 操作符创建索引
CREATE INDEX idx_products_name ON products(data ->> '$.name');

-- 查询
SELECT * FROM products WHERE data ->> '$.name' = 'iPhone';

-- 使用 JSON 替代传统的 EAV 表
CREATE TABLE entities (
    id INTEGER PRIMARY KEY,
    type TEXT NOT NULL,
    attributes TEXT NOT NULL  -- JSON
);

INSERT INTO entities VALUES
    (1, 'user', '{"name":"张三","age":25,"city":"北京"}'),
    (2, 'user', '{"name":"李四","age":30,"city":"上海","phone":"13800138000"}'),
    (3, 'product', '{"name":"iPhone","price":5999}');

-- 查询：用户的城市
SELECT id, attributes ->> '$.city' AS city
FROM entities WHERE type = 'user';

-- 筛选：年龄大于 25 的用户
SELECT * FROM entities
WHERE type = 'user' AND CAST(attributes ->> '$.age' AS INTEGER) > 25;

CREATE TABLE form_submissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    form_name TEXT NOT NULL,
    submitted_at TEXT DEFAULT (datetime('now')),
    data TEXT NOT NULL  -- JSON 表单数据
);

INSERT INTO form_submissions (form_name, data) VALUES
    ('registration', '{"name":"张三","email":"[email protected]","plan":"pro"}'),
    ('registration', '{"name":"李四","email":"[email protected]","plan":"free"}');

-- 查询特定计划的注册
SELECT * FROM form_submissions
WHERE form_name = 'registration'
AND data ->> '$.plan' = 'pro';

CREATE TABLE app_settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

INSERT INTO app_settings VALUES
    ('theme', '{"mode":"dark","primary":"#1976D2","font_size":14}'),
    ('notifications', '{"email":true,"push":false,"sms":true}');

-- 更新单个配置项
UPDATE app_settings
SET value = json_set(value, '$.font_size', 16)
WHERE key = 'theme';

-- 查询配置
SELECT value ->> '$.mode' FROM app_settings WHERE key = 'theme';

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    attrs TEXT DEFAULT '{}'  -- JSON 动态属性
);

-- 创建属性索引
CREATE INDEX idx_products_brand ON products(json_extract(attrs, '$.brand'));
CREATE INDEX idx_products_color ON products(json_extract(attrs, '$.color'));

-- 插入不同类型的属性
INSERT INTO products (name, price, attrs) VALUES
    ('iPhone 15', 599900, '{"brand":"Apple","color":"黑色","storage":"256GB","5g":true}'),
    ('MacBook Pro', 1299900, '{"brand":"Apple","cpu":"M3","ram":"16GB","screen":"14寸"}');

-- 按品牌查询
SELECT * FROM products WHERE json_extract(attrs, '$.brand') = 'Apple';

-- 聚合属性
SELECT json_group_object(key, value)
FROM products, json_each(products.attrs)
WHERE id = 1;

CREATE TABLE webhook_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    payload TEXT NOT NULL,
    processed INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now'))
);

-- 存储事件
INSERT INTO webhook_events (event_type, payload) VALUES
    ('order.created', '{"order_id":12345,"amount":9999,"user_id":42}'),
    ('user.updated', '{"user_id":42,"changes":{"email":"[email protected]"}}');

-- 处理特定事件
SELECT * FROM webhook_events
WHERE event_type = 'order.created'
AND payload ->> '$.amount' > 5000
AND processed = 0;