雖然 SQLite 是一個嵌入式數據庫，但是它支持 JSON 存儲，并且通過 JSON1 擴展插件提供了許多 JSON 函數和運算符；同時，SQLite 表達式索引（Indexes On Expressions）和生成列（Generated Column）為 JSON 數據提供了索引支持，從而實現了文檔存儲和處理功能。
本文給大家介紹一下如何將 SQLite 作為一個文檔數據庫使用。
一個文檔存儲案例
我們首先來看一個簡單的案例：
sqlite>?create?table?docs( ? ...> id int not null primary key, ? ...> content text ? ...> sqlite> insert into docs(id, content) ? ...> values (1, json('{"name":"apple", "price":6.50}'));
首先，我們創建了一個測試表 docs；其中 content 字段用于存儲 JSON 文檔，字段類型為 TEXT。
然后，我們使用 json() 函數確保了輸入字符串符合 JSON 格式要求，如果參數不滿足 JSON 格式要求將會返回錯誤。例如：
sqlite> select json('"not a valid json string');Error: malformed JSON
接下來我們可以在查詢中使用 JSON 文檔中的內容：
sqlite> select * from docs ? ...> where json_extract(content, '$.name') = 'apple'; 1|{"name":"apple","price":6.50}
json_extract() 函數用于從 JSON 文檔中返回 name 節點的數據，具體的函數介紹參考下文。
如果想要對以上查詢進行優化，可以使用表達式索引。例如：
sqlite> create index docs_name on docs(json_extract(content, '$.name'));
sqlite> explain query plan ? ...> select * from docs ???...>?where?json_extract(content,?'$.name')?=?'apple';QUERY PLAN`--SEARCH TABLE docs USING INDEX docs_name (<expr>=?)
我們對文檔 content 中的 name 節點進行了索引，查詢時可以通過索引提高檢索的速度。
目前還有一個問題，SQLite 并沒有提供原始的 JSON 數據類型，content 字段中仍然可以插入任何數據。這個問題我們可以通過生成列來解決。例如：
sqlite>?drop?table?docs;
sqlite> create table docs( ? ...> content text, ? ...> id int generated always as (json_extract(content, '$.id')) virtual not null ? ...> ) sqlite> insert into docs(content) ? ...> values (json('{"id":1, "name":"apple", "price":6.50}'));
我們將 id 字段定義為一個非空的虛擬生成列，數據來自于 content 字段而不會占用額外的存儲。json_extract() 函數的使用意味著插入無效的 JSON 文檔同樣會返回 Error: malformed JSON 錯誤信息。例如：
sqlite> insert into docs(content) values (json('{"id":1, "name":"apple", "price":6.50]}'));Error:?malformed?JSON
sqlite> insert into docs(content) values (json('{"name":"apple", "price":6.50}'));Error: NOT NULL constraint failed: docs.id
第一個錯誤是因為文檔不是有效的 JSON 格式，第二個錯誤是因為文檔中沒有 id 節點。
由于 SQLite 生成列無法作為主鍵字段，我們不能將 id 字段定義為該表的主鍵。不過，我們可以為 id 字段創建一個唯一索引，加上非空約束后的效果和主鍵一樣。
sqlite>?create?unique?index?docs_id?on?docs(id);
sqlite> insert into docs(content) values (json('{"id":1, "name":"banana", "price":8.00}'));Error: UNIQUE constraint failed: docs.id
接下來我們詳細介紹一下 JSON1 插件。
JSON1 插件概述
json1 插件是一個可加載的擴展，實現了 15 個應用程序定義的 SQL 函數和 2 個表值函數，可以用于管理 SQLite 中的 JSON 文檔。其中，以下 13 個函數是標量函數：
json(json)，驗證輸入參數是否符合 JSON 格式并返回結果。
json_array(value1,value2,...)，創建一個 JSON 數組。
json_array_length(json)，返回 JSON 數組中的元素個數。
json_array_length(json,path)，返回指定路徑上的 JSON 數組中的元素個數。
json_extract(json,path,...)，提取指定路徑上的元素。
json_insert(json,path,value,...)，在指定路徑上插入元素。
json_object(label1,value1,...)，創建一個 JSON 對象。
json_patch(json1,json2)，增加、修改或者刪除 JSON 對象中的元素。
json_remove(json,path,...)，刪除指定路徑上的元素。
json_replace(json,path,value,...)，替換指定路徑上的元素。
json_set(json,path,value,...)，設置指定路徑上的元素。
json_type(json)，返回最外層元素的 JSON 數據類型。
json_type(json,path)，返回指定路徑上的元素的 JSON 數據類型。
json_valid(json)，驗證輸入參數是否符合 JSON 格式。
json_quote(value)，將 SQL 數據轉換為 JSON 格式。
以下 2 個是表值函數：
json_each(json) 和 json_each(json,path)，將 JSON 元素轉換為 SQL 數據行。
json_tree(json) 和 json_tree(json,path)，遞歸遍歷 JSON 元素并轉換為 SQL 數據行。
json1 插件目前使用文本存儲 JSON 數據。向下兼容意味著 SQLite 只能存儲 NULL、整數、浮點數、文本以及 BLOB，無法增加第 6 個類型“JSON”。
json1 插件目前不支持 JSON 文檔的二進制編碼（BSON）。經過試驗沒有找到比純文本編碼格式明顯更小或者更快的二進制編碼，目前的實現可以支持 1GB/s 的 JSON 文本解析。所有的 json1 函數參數都不接受 BLOB，如果指定這種參數將會報錯，因為 BLOB 是為了將來增強而保留的二進制 JSON 存儲類型。
json1 擴展名中的數字“1”是故意設計的，設計人員預計將來會基于 json1 的經驗創建新的不兼容的 JSON 擴展。一旦獲得足夠的經驗，某種JSON 擴展可能會被添加到 SQLite 核心代碼中。目前，對 JSON 的支持仍然是通過擴展的形式實現。
通用參數說明
對于第一個參數是 JSON 的函數，該參數可以是一個 JSON 對象、數組、數字、字符串或者 null。SQLite 數字和 NULL 值分別被當作 JSON 數字和 null，SQLite 文本可以被當作? JSON 對象、數組或者字符串。如果 SQLite 本文不符合 JSON 對象、數組或者字符串格式，函數將會返回錯誤， json_valid() 和 json_quote() 函數除外。
為了驗證格式的有效性，JSON 輸入參數中開頭和結尾的空白字符將會被忽略。根據 JSON 規范，內部的空白字符也會被忽略。這些函數完全遵循 RFC-7159 JSON 語法。
對于接受 PATH 參數的函數，PATH 必須滿足一定的格式，否則函數將會返回錯誤。滿足格式的 PATH 必須是一個以“\$”符號開頭，加上零個或多個“.objectlabel”或者“[arrayindex]”組成的文本。
其中，arrayindex 通常是一個非負的整數 N，表示選擇數組的第 N 個元素，從 0 開始計數。arrayindex 也可以使用“#-N”的形式，表示選擇從右邊開始的第 N 個元素。數組最后一個元素是“#-1”，字符“#”相當于數據元素的個數。
對于接受 value 參數（value1，value2 等）的函數，這些參數通常被當作引號引用的字符串常量，并且最終解析為 JSON 字符串數據。不過，如果某個 value 參數直接來自另一個 json1 函數的輸出結果，那么該參數將被當作實際的 JSON，傳入的將會是完整的 JSON 而不是字符串常量。
例如，在下面的 json_object() 函數調用中，value 參數看起來像是一個滿足格式的 JSON 數組。但是，由于它是一個普通的 SQL 文本，因此被解析為一個字符串常量，并且作為一個字符串被添加到結果中：
SELECT json_object('ex','[52,3.14159]');json_object('ex','[52,3.14159]')|--------------------------------|{"ex":"[52,3.14159]"} ? ? ? ? ? |
但是，如果一個外部 json_object() 調用中的 value 參數來自另一個函數的結果，例如 json() 或者 json_array()，將會被解析為實際的 JSON 并且作為 JSON 添加到結果中：
SELECT json_object('ex',json('[52,3.14159]'));json_object('ex',json('[52,3.14159]'))|--------------------------------------|{"ex":[52,3.14159]} ? ? ? ? ? ? ? ? ? |
SELECT json_object('ex',json_array(52,3.14159));json_object('ex',json_array(52,3.14159))|----------------------------------------|{"ex":[52,3.14159]} ? ? ? ? ? ? ? ? ? ? |
總之，json 參數總是被解釋為 JSON，無論該參數的值來自何處。但是 value 參數只有當其直接來自另一個 json1 函數時才被解釋為 JSON。
JSON 函數說明
接下來我們詳細介紹 json1 擴展中的各種函數。
json()
json(X) 函數可以驗證參數 X 符合 JSON 字符串的格式，并且返回一個精簡版的 JSON 字符串（刪除了所有不必要的空白字符）。如果 X 不是一個格式正確的 JSON 字符串，函數將會返回錯誤。
如果參數 X 是一個包含重復標簽的 JSON 對象，不確定是否保留重復元素。當前實現保留了重復元素，但是將來可能會刪除重復元素，而且沒有提示。例如：
SELECT json(' { "this" : "is", "a": [ "test" ] } ') AS doc;doc ? ? ? ? ? ? ? ? ? ? ? |--------------------------|{"this":"is","a":["test"]}|
SELECT json(' { "this" : "is", "a": [ "test" } ') AS doc;SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (malformed JSON)
json_array()
json_array(value1,value2,...) 函數接收零個或多個參數，并且返回一個由這些參數組成的 JSON 數組。如果任何參數是 BLOB，函數將會返回錯誤。
TEXT 類型的參數通常會轉換為引號包含的 JSON 字符串。但是，如果該參數來自其他 json1 函數的輸出，將會作為 JSON 傳入。這種處理方式可以實現 json_array() 和 json_object() 函數的嵌套調用。json() 函數也可以將字符串轉換為 JSON。
例如：
SELECT json_array(1,2,'3',4) AS doc;doc ? ? ? ?|-----------|[1,2,"3",4]|
SELECT json_array('[1,2]') AS doc;doc ? ? ?|---------|["[1,2]"]|
SELECT json_array(json_array(1,2)) AS doc;doc ? ?|-------|[[1,2]]|
SELECT json_array(1,null,'3','[4,5]','{"six":7.7}') AS doc;doc ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |------------------------------------|[1,null,"3","[4,5]","{\"six\":7.7}"]|
SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) AS doc;doc ? ? ? ? ? ? ? ? ? ? ? ? ? |------------------------------|[1,null,"3",[4,5],{"six":7.7}]|
json_array_length()
json_array_length(X) 函數返回 JSON 數組 X 中的元素個數，如果 X 是其他 JSON 數據而不是數組時返回 0。json_array_length(X,P) 函數返回路徑 P 對應數組中的元素個數，如果 X 或者路徑 P 對應的是其他 JSON 數據而不是數組時返回 0，如果路徑 P 沒有對應的元素時返回 NULL。如果 X 不是一個格式正確的 JSON 字符串，或者 P 不是一個格式正確的路徑，函數將會返回錯誤。
例如：
SELECT json_array_length('[1,2,3,4]') AS length;length|------|?????4|
SELECT json_array_length('[1,2,3,4]', '$') AS length;length|------| ? ? 4|
SELECT json_array_length('[1,2,3,4]', '$[2]') AS length;length|------|?????0|
SELECT json_array_length('{"one":[1,2,3]}') AS length;length|------|?????0|
SELECT?json_array_length('{"one":[1,2,3]}',?'$.one')?AS?length;
SELECT json_array_length('{"one":[1,2,3]}', '$.two') AS length;length|------| ? ? ?|
json_extract()
json_extract(X,P1,P2,...) 函數提取并返回 JSON 數據 X 中的一個或多個元素。如果只提供了路徑 P1，對于 JSON null 返回的數據類型為 NULL，對于 JSON 數字返回的數據類型為 INTEGER 或者 REAL，對于 JSON false 返回的數據為 INTEGER 類型的 0，對于 JSON true 返回的數據為 INTEGER 類型的 1，對于 JSON 字符串返回的數據類型為去掉引號的文本，對于 JSON 對象和數組返回的是它們的文本形式。如果指定了多個路徑參數（P1、P2 等），函數將會返回 SQLite 文本形式的 JSON 數組，包含了每個路徑對應的數據。
例如：
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') AS doc;doc ? ? ? ? ? ? ? ? ? ? ?|-------------------------|{"a":2,"c":[4,5,{"f":7}]}|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') AS doc;doc ? ? ? ? ?|-------------|[4,5,{"f":7}]|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') AS doc;doc ? ?|-------|{"f":7}|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') AS doc;doc|---|??7|
SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') AS doc;doc ? ? ?|---------|[[4,5],2]|
SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') AS doc;doc|---|??5|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') AS doc;doc|---|???|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') AS doc;doc ? ? |--------|[null,2]|
json_insert()、json_replace 和 json_set()
json_insert(json,path,value,...)、json_replace(json,path,value,...) 和 json_set(json,path,value,...) 函數的第一個參數是一個 JSON 數據，加上零個或多個路徑和數據的參數對，使用 path/value 參數對更新輸入的 JSON 數據后返回一個新的 JSON 字符串。這些函數的區別僅僅在于創建新值和覆蓋舊值得方式不同。
| 函數 | 是否覆蓋已有元素 | 是否創建不存在的元素 |
|---|
| json_insert() | ? | ?? |
| json_replace() | ?? | ? |
| json_set() | ?? | ?? |
這三個函數參數的個數總是奇數，第一個參數總是需要修改的原始 JSON。隨后的參數成對出現，每對參數中的第一個是路徑，第二個是在該路徑上插入、替換或者設置的數據。
數據的修改按照從左至右的順序執行，前面的數據更改會影響后續的路徑搜索。
如果某個 path/value 參數對中的數據是 TEXT 類型，通常來說將會作為一個引號引用的 JSON 字符串插入，即使這個字符串看起來像有效的 JSON。不過，如果該數據值另一個 json1 函數（例如 json()、json_array() 或者 json_object()）的結果，將被解釋為一個 JSON 插入并且保留所有的子結構。
如果第一個參數不是一個格式正確的 JSON，或者任何 PATH 不是一個格式正確的路徑，或者任何參數是 BLOB，函數將會返回錯誤。
如果想要在數據的最后追加元素，可以使用 json_insert() 函數并且指定索引下標“#”。例如：
SELECT json_insert('[1,2,3,4]','$[#]',99) AS doc;doc ? ? ? ? |------------|[1,2,3,4,99]|
SELECT json_insert('[1,[2,3],4]','$[1][#]',99) AS doc;doc ? ? ? ? ? |--------------|[1,[2,3,99],4]|
其他示例：
SELECT json_insert('{"a":2,"c":4}', '$.a', 99) AS doc;doc ? ? ? ? ?|-------------|{"a":2,"c":4}|
SELECT json_insert('{"a":2,"c":4}', '$.e', 99) AS doc;doc ? ? ? ? ? ? ? ? |--------------------|{"a":2,"c":4,"e":99}|
SELECT json_replace('{"a":2,"c":4}', '$.a', 99) AS doc;doc ? ? ? ? ? |--------------|{"a":99,"c":4}|
SELECT json_replace('{"a":2,"c":4}', '$.e', 99) AS doc;doc ? ? ? ? ?|-------------|{"a":2,"c":4}|
SELECT json_set('{"a":2,"c":4}', '$.a', 99) AS doc;doc ? ? ? ? ? |--------------|{"a":99,"c":4}|
SELECT json_set('{"a":2,"c":4}', '$.e', 99) AS doc;doc ? ? ? ? ? ? ? ? |--------------------|{"a":2,"c":4,"e":99}|
SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]') AS doc;doc ? ? ? ? ? ? ? ? ?|---------------------|{"a":2,"c":"[97,96]"}|
SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) AS doc;doc ? ? ? ? ? ? ? ?|-------------------|{"a":2,"c":[97,96]}|
SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) AS doc;doc ? ? ? ? ? ? ? ?|-------------------|{"a":2,"c":[97,96]}|
json_object()
json_object(label1,value1,...) 函數接收零個或多個參數對，并且返回一個由這些參數組成的 JSON 對象。。每對參數中的第一個是元素標簽，第二個是對應的數據。如果任何參數是 BLOB，函數將會返回錯誤。
json_object() 函數目前可以接受重復的元素標簽，將來可能不允許。
如果只傳入一個 TEXT 類型的參數，即使它是一個格式正確的 JSON，通常也會被轉換為引號引用的 JSON 字符串。不過，如果該參數直接來自其他 json1 函數的輸出，將被被當作 JSON 處理，所有的類型信息和子結構都會保留。這種處理方式可以實現 json_array() 和 json_object() 函數的嵌套調用。json() 函數也可以將字符串轉換為 JSON。
例如：
SELECT json_object('a',2,'c',4) AS doc;doc ? ? ? ? ?|-------------|{"a":2,"c":4}|
SELECT json_object('a',2,'c','{e:5}') AS doc;doc ? ? ? ? ? ? ? ?|-------------------|{"a":2,"c":"{e:5}"}|
SELECT json_object('a',2,'c',json_object('e',5)) AS doc;doc ? ? ? ? ? ? ? ?|-------------------|{"a":2,"c":{"e":5}}|
json_patch()
json_patch(T,P) 函數利用 RFC-7396 MergePatch 算法將補丁 P 應用到輸入 T，返回修補之后的 T 副本。
MergePatch 可以增加、修改或者刪除 JSON 對象中的元素，因此對于 JSON 對象，json_patch() 函數一般可以作為 json_set() 和 json_remove() 函數的替代。不過，MergePatch 將 JSON 數組當作原子對象處理，不能追加或者修改數組中的單個元素，只能將整個數組作為一個單元進行插入、替換或者刪除。因此，json_patch() 對于處理包含數組（尤其是數組中包含很多子結構）的 JSON 用處不大。
例如：
SELECT json_patch('{"a":1,"b":2}','{"c":3,"d":4}') AS doc;doc ? ? ? ? ? ? ? ? ? ? ?|-------------------------|{"a":1,"b":2,"c":3,"d":4}|
SELECT json_patch('{"a":[1,2],"b":2}','{"a":9}') AS doc;doc ? ? ? ? ?|-------------|{"a":9,"b":2}|
SELECT json_patch('{"a":[1,2],"b":2}','{"a":null}') AS doc;doc ? ?|-------|{"b":2}|
SELECT json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') AS doc;doc ? ? ? ? ?|-------------|{"a":9,"c":8}|
SELECT json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') AS doc;doc ? ? ? ? ? ? ? ? ? ? ? ? ? ?|-------------------------------|{"a":{"x":1,"y":9},"b":3,"c":8}|
json_remove()
json_remove(X,P,...) 函數第一個參數 X 是一個 JSON 數據，加上零個或多個路徑參數 P，返回一個刪除指定元素后的 JSON。如果指定路徑上沒有對應的元素，忽略該參數。
數據的刪除按照從左至右的順序執行，前面的數據更改會影響后續的路徑搜索。
如果沒有指定路徑參數，json_remove(X) 函數將會返回格式化后的 X，刪除了多余的空白字符。
如果第一個參數不是一個格式正確的 JSON，或者任何 PATH 不是一個格式正確的路徑，或者任何參數是 BLOB，函數將會返回錯誤。
例如：
SELECT json_remove('[0,1,2,3,4]','$[2]') AS doc;doc ? ? ?|---------|[0,1,3,4]|
SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]') AS doc;doc ? ?|-------|[1,3,4]|
SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]') AS doc;doc ? ?|-------|[1,2,4]|
SELECT json_remove('[0,1,2,3,4]','$[#-1]','$[0]') AS doc;doc ? ?|-------|[1,2,3]|
SELECT json_remove('{"x":25,"y":42}') AS doc;doc ? ? ? ? ? ?|---------------|{"x":25,"y":42}|
SELECT json_remove('{"x":25,"y":42}','$.z') AS doc;doc ? ? ? ? ? ?|---------------|{"x":25,"y":42}|
SELECT json_remove('{"x":25,"y":42}','$.y') AS doc;doc ? ? |--------|{"x":25}|
SELECT json_remove('{"x":25,"y":42}','$') AS doc;doc|---| ? |
json_type()
json_type(X) 函數返回 X 最外層元素的 JSON 數據類型。json_type(X,P) 函數返回路徑 P 對應元素的 JSON 數據類型。json_type() 函數返回的結果為以下字符串之一：'null'、'true'、'false'、'integer'、'real'、'text'、'array' 或者 'object'。如果 json_type(X,P) 函數中的路徑 P 對應的元素不存在，函數將會返回 NULL。
如果參數不是一個格式正確的 JSON，或者參數是 BLOB，函數將會返回錯誤。
例如：
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}') AS type;type ?|------|object|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$') AS type;type ?|------|object|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a') AS type;type |-----|array|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]') AS type;type ? |-------|integer|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]') AS type;type|----|real|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]') AS type;type|----|true|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]') AS type;type |-----|false|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]') AS type;type|----|null|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]') AS type;type|----|text|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]') AS type;type|----| ? ?|
json_valid()
json_valid(X) 函數用于驗證參數的格式。如果 X 是一個格式正確的 JSON，函數返回 1；否則，函數返回 0。
例如：
SELECT json_valid('{"x":35}') AS is_json;is_json|-------|??????1|
SELECT json_valid('{"x":35') AS is_json;is_json|-------| ? ? ?0|
json_quote()
json_quote(X) 函數將 SQL 數據 X（一個數字或者字符串）轉換為對應的 JSON 形式。例如：
SELECT json_quote(3.14159) AS json;json ? |-------|3.14159|
SELECT json_quote('verdant') AS json;json ? ? |---------|"verdant"|
json_group_array() 和 json_group_object()
json_group_array(X) 函數是一個聚合函數，返回一個由所有 X 構成的 JSON 數組。例如：
SELECT json_group_array(X)FROM ( ?SELECT json_array(1,2) AS X ?UNION ALL ?SELECT json_array(3,4) ?UNION ALL ?SELECT 5) t;json_group_array(X)|-------------------|[[1,2],[3,4],5] ? ?|
與此類似，json_group_object(NAME,VALUE) 也是一個聚合函數，返回一個由所有 NAME/VALUE 對組成的 JSON 對象。例如：
SELECT json_group_object(name, value)FROM ( ?SELECT 'first' AS name, json_object('a',2,'c',4) AS value ?UNION ALL ?SELECT 'rgb', json_array(255,255,255) ?UNION ALL ?SELECT 'id', 100) t;json_group_object(name, value) ? ? ? ? ? ? ? ? ? ? ?|----------------------------------------------------|{"first":{"a":2,"c":4},"rgb":[255,255,255],"id":100}|
json_each() 和 json_tree()
json_each(X) 和 json_tree(X) 表值函數將輸入參數 X 中的每個元素轉換為一行數據。json_each(X) 函數只遍歷頂層 JSON 數組或者對象的直接子節點，如果頂層元素是一個基本值則只返回該節點自身。json_tree(X) 函數從頂層元素開始遞歸遍歷所有的 JSON 子結構。
json_each(X,P) 和 json_tree(X,P) 函數和上面兩個函數類似，只是它們將路徑 P 對應的元素作為頂層元素。
json_each() 和 json_tree() 函數返回的表結構如下：
CREATE TABLE json_tree( ? ?key ANY, ? ? ? ? ? ? -- key for current element relative to its parent ? ?value ANY, ? ? ? ? ? -- value for the current element ? ?type TEXT, ? ? ? ? ? -- 'object','array','string','integer', etc. ? ?atom ANY, ? ? ? ? ? ?-- value for primitive types, null for array & object ? ?id INTEGER, ? ? ? ? ?-- integer ID for this element ? ?parent INTEGER, ? ? ?-- integer ID for the parent of this element ? ?fullkey TEXT, ? ? ? ?-- full path describing the current element ? ?path TEXT, ? ? ? ? ? -- path to the container of the current row ? ?json JSON HIDDEN, ? ?-- 1st input parameter: the raw JSON ? ?root TEXT HIDDEN ? ? -- 2nd input parameter: the PATH at which to start);
字段 key 是 JSON 數組中每個元素的下標，或者 JSON 對象中每個元素的標簽。其他情況下，key 字段為空。
字段 atom 是基本元素（除了 JSON 數組和對象之外的元素）對應的 SQL 值，JSON 數組和對象的 atom 字段為空。對于基本 JSON 元素而言，字段 value 的值和 atom 字段相同；對于 JSON 數組和對象元素而言，字段 value 是文本格式的 JSON 數據。
字段 type 的值是一個 SQL 文本，根據 JSON 元素的類型不同可能的取值為 'null'、'true'、'false'、'integer'、'real'、'text'、'array' 或者 'object'。
字段 id 是一個整數，標識了 JSON 字符串中的每個的 JSON 元素。id 是一個內部生成的編號，計算方法在將來的版本中可能會發生改變。唯一可以確認的是每一行都會有一個不同的編號。
字段 parent 對于 json_each() 函數總是返回 NULL。對于 json_tree() 函數，字段 parent 是當前元素的父節點 id；如果是頂層元素，字段的值為 NULL。
字段 fullkey 是一個文本值，標識了當前元素在原始 JSON 字符串中的路徑。即使通過參數 root 提供了其他的起點，也會返回從真正的頂層元素開始的完整路徑。
字段 path 是到包含當前行的數組或對象容器的路徑，或者頂層元素是一個基本類型時（意味著函數只返回當前行）到當前行的路徑。
假設存在以下 user 表：
CREATE TABLE user(name, phone);INSERT INTO user(name, phone) VALUES ('anne', json_array('010-12345678', '020-10003333'));INSERT INTO user(name, phone) VALUES ('tony', json_array('010-12349999', '800-10007777'));
字段 phone 中使用 JSON 數組的形式存儲了零個或多個電話號碼。以下語句可以找出電話號碼以 020 開頭的用戶：
SELECT DISTINCT user.name ?FROM user, json_each(user.phone) WHERE json_each.value LIKE '010-%';name|----|anne|
現在假設當用戶只有一個電話號碼時，字段 phone 中存儲的是普通文本。例如：
INSERT INTO user(name, phone) VALUES ('kevin', '020-10005555');
現在同樣需要找出電話號碼以 020 開頭的用戶。由于 json_each() 函數要求第一個參數是一個格式正確的 JSON，因此它只能用于包含 2 個或更多電話號碼的用戶：
SELECT name FROM user WHERE phone LIKE '020-%'UNION ALLSELECT user.name ?FROM user, json_each(user.phone) WHERE json_valid(user.phone) ? AND json_each.value LIKE '020-%';name |-----| anne|kevin|
假設存在以下 big 表：
CREATE TABLE big(json JSON);INSERT INTO big(json) VALUES (json_object('name', 'anne','phone', json_array('010-12345678', '020-10003333')));INSERT INTO big(json) VALUES (json_object('name', 'tony','phone', json_array('010-12349999', '800-10007777')));
如果想要逐行返回數據中的內容，可以執行以下語句：
SELECT big.rowid, fullkey, value ?FROM big, json_tree(big.json) WHERE json_tree.type NOT IN ('object','array');rowid|fullkey ? |value ? ? ? |-----|----------|------------| ? ?1|$.name ? ?|anne ? ? ? ?| ? ?1|$.phone[0]|010-12345678| ? ?1|$.phone[1]|020-10003333| ? ?2|$.name ? ?|tony ? ? ? ?| ? ?2|$.phone[0]|010-12349999| ? ?2|$.phone[1]|800-10007777|
查詢條件中的 type NOT IN ('object','array') 從結果中去除了容器節點，只返回了葉子元素。我們也可以使用以下語句實現相同的效果：
SELECT big.rowid, fullkey, atom ?FROM big, json_tree(big.json) WHERE atom IS NOT NULL;
假設 big.json 字段中的每一行是一個 JSON 對象，包含一個唯一標識節點'\$.id' 和一個嵌套其他對象的 '\$.partlist' 節點。例如：
INSERT INTO big(json) VALUES (json_object('id', 1,'partlist', json_array('6fa5181e-5721-11e5-a04e-57f3d7b32808', 'a18437b3-b6c4-4473-a9c5-50e7b8eef6be')));INSERT INTO big(json) VALUES (json_object('id', 2,'partlist', json_object('uuid','6fa5181e-5721-11e5-a04e-57f3d7b32808')));INSERT INTO big(json) VALUES (json_object('id', 3,'partlist', json_array(json_object('uuid','e7e3845d-cdfe-48aa-877f-9121b970761d'),json_object('uuid','6fa5181e-5721-11e5-a04e-57f3d7b32808'))));
如果想要找出 '$.partlist' 元素中任意節點包含一個或多個 uuid 為 '6fa5181e-5721-11e5-a04e-57f3d7b32808' 的文檔，可以使用以下語句：
SELECT DISTINCT json_extract(big.json,'$.id') ?FROM big, json_tree(big.json, '$.partlist') WHERE json_tree.key='uuid' ? AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';json_extract(big.json,'$.id')|-----------------------------| ? ? ? ? ? ? ? ? ? ? ? ? ? ?2| ? ? ? ? ? ? ? ? ? ? ? ? ? ?3|
編譯 JSON1 插件
SQLite 可加載擴展文檔描述了如何將可加載擴展編譯為共享庫。文檔中描述的方法也適用于 json1 模塊。
json1 源代碼包含在 SQLite 程序包中，默認沒有啟用編譯。可以使用 -DSQLITE_ENABLE_JSON1?編譯時選項啟用 json1 擴展。編譯命令行工具和測試工具時，標準的 makefile 中包含了該選項，所以命令行工具可以使用 json1。
版本支持
json1 擴展使用了 SQLite 3.9.0 引入的 sqlite3_value_subtype() 和sqlite3_result_subtype() 接口，因此更早版本的 SQLite 無法使用 json1 擴展。
當前的 JSON 庫實現使用了一個遞歸下降語法解析器。為了避免使用過多的堆棧空間，任何超過 2000 層嵌套的 JSON 輸入都被視為無效數據。嵌套級別的限制符合 RFC-7159 section 9?規定的 JSON 兼容實現。
總結
本文介紹了 SQLite 中的文檔存儲功能。我們可以借助于 json1 擴展插件提供的 JSON 函數實現文檔數據的存儲以及 JSON 文檔和 SQL 數據的相互轉換，同時還可以利用 SQLite 表達式索引和生成列為 JSON 數據提供索引支持，從而實現了將 SQLite 作為一個文檔數據庫使用。
該文章在 2024/10/30 14:33:17 編輯過
400 186 1886
