数据库新手，拿 SQLite 练练 CTE
实现了一条语句将 json：
{"书": {"章 1": ["节 1", "节 2"], "章 2": "节 3"}}
逐项添加进表中：
+----+-----------+------+
| id | parent_id | data |
+----+-----------+------+
| 2 | 0 | 书 |
| 4 | 2 | 章 1 |
| 5 | 4 | 节 1 |
| 6 | 4 | 节 2 |
| 7 | 2 | 章 2 |
| 8 | 7 | 节 3 |
+----+-----------+------+
再用一条语句，将某节点（如“节 2”）所在的整棵树查询出来：
+-----------+
| result |
+-----------+
| 书 |
| 章 1 |
| 节 1 |
| 节 2 |
| 章 2 |
| 节 3 |
+-----------+
功力不够，想转化成 json ，却不懂咋写了
CREATE TABLE node (id INTEGER PRIMARY KEY, parent_id INT, data);
INSERT INTO node (parent_id, data) VALUES (0, '书 0'); -- 测试表非空时 下面 INSERT 是否正常
-- =================================
-- 以下将 json 字符串 逐项添加进表中
-- =================================
WITH
my_data(json) AS (
SELECT '{
"书 1": {
"书 1 章 1": ["书 1 章 1 节 1", "书 1 章 1 节 2"],
"书 1 章 2": {
"书 1 章 2 节 1": {"书 1 章 2 节 1 段 1": "书 1 章 2 节 1 段 1 字 1"},
"书 1 章 2 节 2": ["书 1 章 2 节 2 段 1", "书 1 章 2 节 2 段 2"]
}
},
"书 2": ["书 2 章 1", "书 2 章 2"]
}'
),
node_info(max_id) AS (
SELECT IFNULL(max(id), 0) FROM node
)
-- 添加搜集好的 key 和 value
INSERT INTO node (id, parent_id, data)
-- 遇到 object 时，取其 key
SELECT max_id + id - (type NOT IN ('array', 'object')) AS id,
CASE WHEN parent THEN max_id + parent
ELSE 0
END AS parent_id,
key AS data
FROM node_info, my_data, json_tree(my_data.json)
WHERE typeof(key) = 'text'
UNION ALL
-- 不是 array 和 object 时，取其 value
SELECT max_id + id + (parent = 0 AND key IS NULL) AS id,
CASE WHEN typeof(key) = 'text' THEN max_id + id - 1 -- object 的值
WHEN parent THEN max_id + parent -- array 的值
ELSE 0 -- 根处的值
END AS parent_id,
value AS data
FROM node_info, my_data, json_tree(my_data.json)
WHERE type NOT IN ('array', 'object');
SELECT * FROM node;
-- =====================================================
-- 以下将 某节点所在的整棵树 转化成 有缩进的列表 和 json
-- =====================================================
WITH RECURSIVE
my_data(node_id) AS (
SELECT id FROM node WHERE data = '书 1 章 2 节 1 段 1 字 1' LIMIT 1
),
-- 列举 my_data 的 node_id 的所有父节点
parent_of(id, parent_id) AS (
SELECT id, parent_id
FROM my_data JOIN node ON node_id =
node.id UNION ALL
SELECT
p.id, p.parent_id
FROM parent_of n JOIN node p ON n.parent_id =
p.id ),
-- 获取 my_data 的 node_id 的根节点
root(id) AS (
SELECT id FROM parent_of WHERE parent_id = 0
),
-- 列举 tree
list(id, data, level) AS (
SELECT id, data, 0
FROM root JOIN node USING(id)
UNION ALL
SELECT
n.id, n.data, level + 1
FROM node n JOIN list p ON n.parent_id =
p.id ORDER BY 3 DESC, 1
)
-- -- json 化 tree
-- jsonify() AS (
-- -- 功力不够，写不出来
-- )
SELECT format('%*s', level*3, '') || data FROM list;
Select Language