[TOC] ## 概述 SQLite 的 JSON 擴展允許你在 SQLite 數據庫中存儲、查詢和操作 JSON 數據。SQLite 從版本 3.9.0 開始引入了對 JSON 數據的支持，使其能夠直接在 SQL 查詢中處理 JSON 數據。 ## 函數 ### json 1. json(X) 函數驗證其參數 X 是否為有效的 JSON 字符串或 JSONB blob， 2. 如果是json 則返回 JSON 字符串的縮小版本,其中刪除了所有不必要的空格。 3. 如果 X 不是格式正確的 JSON 字符串或 JSONB blob，則此例程會拋出錯誤 ``` select json(' { "this" : "is", "a": [ "test" ] } ') // output {"this":"is","a":["test"]} ``` ### json_pretty 使 JSON 結果更易于人類閱讀 ### json_array 組合成 json 數組 1. 接收一個或多個參數,并返回這些參數組成的 JSON 數組 ``` json_array(1,2,'3',4) → '[1,2,"3",4]' json_array('[1,2]') → '["[1,2]"]' json_array(json_array(1,2)) → '[[1,2]]' json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) → '[1,null,"3",[4,5],{"six":7.7}]' ``` ### json_object 組合成json 對象 示例 ``` json_object('a',2,'c',4) → '{"a":2,"c":4}' json_object('a',2,'c','{e:5}') → '{"a":2,"c":"{e:5}"}' json_object('a',2,'c',json_object('e',5)) → '{"a":2,"c":{"e":5}}' ``` ### json_extract 1. 提取需要的字段值 格式 ``` json_extract(X,P1,P2,...) ``` 示例 ``` json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') → '{"a":2,"c":[4,5,{"f":7}]}' json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') → '[4,5,{"f":7}]' json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') → '{"f":7}' json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') → 7 json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') → '[[4,5],2]' json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') → 5 json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') → NULL json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') → '[null,2]' json_extract('{"a":"xyz"}', '$.a') → 'xyz' json_extract('{"a":null}', '$.a') → NULL ``` ### json_insert()、json_replace 和 json_set() 函數 | 功能 | 如果已經存在則覆蓋？ | 如果不存在則創建？ | | --- | --- | --- | | json\_insert() | 不 | 是的 | | json\_replace() | 是的 | 不 | | json\_set() | 是的 | 是的 | 3. 第一個參數始終是要編輯的原始 JSON。后續參數成對出現，每對的第一個元素是路徑，第二個元素是要在該路徑上插入、替換或設置的值 示例 1. 要將元素附加到數組末尾，請使用數組索引為“#”的 json_insert() ``` json_insert('[1,2,3,4]','$[#]',99) → '[1,2,3,4,99]' json_insert('[1,[2,3],4]','$[1][#]',99) → '[1,[2,3,99],4]' ``` 2. 其他示例： ``` json_insert('{"a":2,"c":4}', '$.a', 99) → '{"a":2,"c":4}' json_insert('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}' json_replace('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}' json_replace('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4}' json_set('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}' json_set('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}' json_set('{"a":2,"c":4}', '$.c', '[97,96]') → '{"a":2,"c":[97,96]"}' json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) → '{"a":2,"c":[97,96]}' json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) → '{"a":2,"c":[97,96]}' ``` ### json_remove 1. json_remove(X,P,...) 函數將單個 JSON 值作為其第一個參數，后跟零個或多個路徑參數 2. son_remove(X,P,...) 函數返回 X 參數的副本，其中刪除了路徑參數標識的所有元素。選擇在 X 中找不到的元素的路徑將被忽略 示例 ``` json_remove('[0,1,2,3,4]','$[2]') → '[0,1,3,4]' json_remove('[0,1,2,3,4]','$[2]','$[0]') → '[1,3,4]' json_remove('[0,1,2,3,4]','$[0]','$[2]') → '[1,2,4]' json_remove('[0,1,2,3,4]','$[#-1]','$[0]') → '[1,2,3]' json_remove('{"x":25,"y":42}') → '{"x":25,"y":42}' json_remove('{"x":25,"y":42}','$.z') → '{"x":25,"y":42}' json_remove('{"x":25,"y":42}','$.y') → '{"x":25}' json_remove('{"x":25,"y":42}','$') → NULL ``` ### json_patch 可以添加、修改或刪除 JSON 對象的元素 1. json_patch() 例程是json_set()和json_remove()的通用替代品 2. MergePatch 不能附加到數組，也不能修改數組的單個元素。它只能將整個數組作為一個單元插入、替換或刪除 示例 ``` json_patch('{"a":1,"b":2}','{"c":3,"d":4}') → '{"a":1,"b":2,"c":3,"d":4}' json_patch('{"a":[1,2],"b":2}','{"a":9}') → '{"a":9,"b":2}' json_patch('{"a":[1,2],"b":2}','{"a":null}') → '{"b":2}' json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') → '{"a":9,"c":8}' json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') → '{"a":{"x":1,"y":9},"b":3,"c":8}' ``` ### json_array_length 1. json_array_length(X) 函數返回 JSON 數組 X 中的元素數量，如果 X 是某種 JSON 值而非數組，則返回 0 2. json_array_length(X,P) 在 X 中的路徑 P 處定位數組并返回該數組的長度 ``` json_array_length('[1,2,3,4]') → 4 json_array_length('[1,2,3,4]', '$') → 4 json_array_length('[1,2,3,4]', '$[2]') → 0 json_array_length('{"one":[1,2,3]}') → 0 json_array_length('{"one":[1,2,3]}', '$.one') → 3 json_array_length('{"one":[1,2,3]}', '$.two') → NULL ``` ### json_type 返回類型 1. json_type() 返回的“類型”是以下 SQL 文本值之一：“null”、“true”、“false”、“integer”、“real”、“text”、“array”或“object” 示例 ``` json_type('{"a":[2,3.5,true,false,null,"x"]}') → 'object' json_type('{"a":[2,3.5,true,false,null,"x"]}','$') → 'object' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a') → 'array' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]') → 'integer' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]') → 'real' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]') → 'true' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]') → 'false' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]') → 'null' json_type（'{“a”：[2,3.5，true，false，null，“x”]}'，'$.a[5]'） →'text' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]') → NULL ``` ### json_quote 1. 用于將一個文本值（TEXT）轉換為一個 JSON 字符串，并為它添加合適的引號。 2. 這個函數主要用于確保字符串在 JSON 表達式中是合法的，以防止 JSON 解析錯誤 示例 ``` json_quote（3.14159） →3.14159 json_quote('verdant') → '"verdant"' json_quote('[1]') → '[1]"' json_quote（json（'[1]'）） →'[1]' json_quote('[1,') → '[1,' ``` ### json_valid 1. 參數 X 是格式正確的 JSON，則 json_valid(X,Y) 函數返回 1 2. 如果 X 格式不正確，則返回 0。 3. Y 參數是一個整數位掩碼，它定義了“格式正確”的含義 ``` 0x01 → 輸入是嚴格遵守規范 RFC-8259 JSON 的文本，沒有任何擴展。 0x02 → 輸入是具有上面描述的JSON5擴展的 JSON 文本。 0x04 → 輸入是一個 BLOB，表面上看起來像是JSONB。 0x08 → 輸入是嚴格符合內部JSONB格式的 BLOB 。 ``` 示例 ``` json_valid('{"x":35}') → 1 json_valid（'{x：35}'） →0 json_valid('{x:35}',6) → 1 json_valid('{"x":35') → 0 json_valid（NULL） →NULL ``` ## 示例 ### 存儲json ``` CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT, data TEXT -- 用于存儲 JSON 數據 ); INSERT INTO users (name, data) VALUES ('Alice', '{"age": 25, "city": "New York"}'); INSERT INTO users (name, data) VALUES ('Bob', '{"age": 30, "city": "San Francisco"}'); ``` ### 查詢 JSON 數據 ``` SELECT name, json_extract(data, '$.age') AS age FROM users; // outut name | age -------|----- Alice | 25 Bob | 30 ``` > $.age 是 JSON 路徑表達式，其中 $ 表示根對象。 ### 更新 JSON 數據 ``` UPDATE users SET data = json_set(data, '$.age', 26) WHERE name = 'Alice'; ``` ### 添加或刪除 JSON 字段 ``` // 添加 UPDATE users SET data = json_set(data, '$.email', 'alice@example.com') WHERE name = 'Alice'; // 刪除 UPDATE users SET data = json_remove(data, '$.city') WHERE name = 'Bob'; ``` ### 查詢嵌套的 JSON 數據 ``` INSERT INTO users (name, data) VALUES ('Charlie', '{"age": 28, "address": {"city": "Los Angeles", "zip": "90001"}}'); SELECT name, json_extract(data, '$.address.city') AS city FROM users WHERE name = 'Charlie'; // output name | city --------|--------- Charlie | Los Angeles ``` ### 聚合和過濾 JSON 數據 ``` SELECT name, json_extract(data, '$.age') AS age FROM users WHERE json_extract(data, '$.age') > 25; ``` ### 遞歸處理數據 可處理類似組織架構等的數據 ``` WITH json_data AS ( -- 在這里定義 JSON 數據 SELECT '{ "id": 1, "name": "CEO", "users": [ { "id": 2, "name": "Manager", "users": [ { "id": 3, "name": "Developer" }, { "id": 4, "name": "Designer" } ] }, { "id": 5, "name": "HR" } ] }' AS data ), org_tree AS ( -- 遞歸解析 JSON 數據 SELECT json_extract(json_data.data, '$.id') AS id, json_extract(json_data.data, '$.name') AS name, json_extract(json_data.data, '$.users') AS json_data FROM json_data UNION ALL SELECT json_extract(value, '$.id') AS id, json_extract(value, '$.name') AS name, json_extract(value, '$.users') AS json_data FROM org_tree, json_each(org_tree.json_data) ) SELECT id, name,json_data FROM org_tree; // output 1 CEO [{"id":2,"name":"Manager","users":[{"id":3,"name":"Developer"},{"id":4,"name":"Designer"}]},{"id":5,"name":"HR"}] 2 Manager [{"id":3,"name":"Developer"},{"id":4,"name":"Designer"}] 5 HR 3 Developer 4 Designer ```